Assign Function to a Command Button

[MUEEN SEHDI]

How can I assign a function to a command button placed in Excel 2007 Worksheet?

I need to use Goal Seek command (from Excel Menu/Data/What-If Analysis/Goal
Seek in my worksheet to do some calculation. However instead of following the
menu path, I want to place a button in my worksheet and assign the Goal seek
command to this button. By clicking on this button, the dialogue for Goal
Seek function should appear.

 

[Dave Peterson]

First, I used xl2003--I'm not sure the dialogs are the same in xl2007.

Second, you don't assign a function to a commandbutton, you should be able to
just doubleclick on it (while in design mode) and see the commandbutton1_click
event. You should see something like this in the newly opened window:

Option Explicit
Private Sub CommandButton1_Click()

End Sub

Change it to look like:

Option Explicit
Private Sub CommandButton1_Click()
Application.Dialogs(xlDialogGoalSeek).Show
End Sub

Then back to excel, leave design mode and test the commandbutton.

===========
If you really wanted to assign the macro to the button, then you have to use a
button from the Forms toolbar --not the commandbutton from the control toolbox
toolbar.

The code would go into a general module--not the worksheet module.

And would look something like:

Option Explicit
Sub testme()
Application.Dialogs(xlDialogGoalSeek).Show
End Sub


And then you could just rightclick on the button and choose assign macro.