macro activate button to run another macro ?

[bdsii]

I have one macro that runs calculations and displays the results inside a spreadsheet in rows and columns.

I would like to be able to have the original macro enable or activate a button that can be clicked which would then initiate a second macro to perform additional calculations from the results of the first macro. The second macro would be run frequently and needs to have a simple button to click to start rather than trying to run it through the the macro menu of the spreadsheet.

Currently I have buttons for the user to initiate the first macro and a second buttton is not hard to create and assign the second macro but I would like for that button to be inactive or "grayed out" for the user until the first macro runs providing the data needed by the second macro.

So.....can anyone suggest code that can be run from inside one macro to create or enable a button to run a second macro ?

I have tried doing this using the record feature in the spreadsheet but I cannot get the insert shape function to work and cannot assign a macro to an existing shape which was a workaround for enabling a button.

Any ideas ?

Thanks !!

[Bob Phillips]

What sort of buttons, orms toolbar or Controls Toolbox?

I wouldn't try to add buttons on the fly, just disable them and enable when required.

[Simon Lloyd]

maybe this will help http://www.vbaexpress.com/kb/getarticle.php?kb_id=940 its a little piece of code that can split your macro in two, the first click of the button runs the first section and the second click runs the subsequent portion.

[bdsii]

The buttons I am using is are simple ones I create from inserting a shape into the worksheet and then using them to activate the macro code.

The reason the code is split in two sections is that the first section is dependant upon data that will be provided. The second section of code runs a "what-if" macro that provides updates to the original info based upon user input. they add info click the new activated button and a new answer is provided. They can then edit the info they just put in, click the new button again and get a revised answer.

Thanks Simon....will have to give the link a look and see if it will work. However, the idea is for the user to change the data they are providing after the first macro runs. If they wanted a new what-if scenario they shouldn't have to run the first macro again just to get to the second macro to provide them with a new answer.

Any ideas ?

[Bob Phillips]

I would add forms buttons and use code like so



Public Sub Button1_Click()
With ActiveSheet.Buttons("Button 2")

.Font.ColorIndex = 1
.Enabled = True
End With
End Sub

Public Sub Button2_Click()
MsgBox "hello2"
End Sub