MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Drop down menu for running macros


Posted by Tee on May 09, 2001 3:40 PM

Hi One and All

I have a spreadsheet set up as a menu that is displayed when Excel opens.
What I would like to do is have a combo box that the user can select an option that will run a macro.

eg.
"Print" Combo box

Macro "Print all records"
Macro "Print January Records"

The user can select "Print all Records" and once selected, the macro will
run.

Many thanks in advance for any the help.

Regards
Tee.


Posted by Dave Hawley on May 09, 2001 4:00 PM


Hi Tee

There are a few ways to do this;

1 Validation.

Go to Data>Validation and select the "List" option. Then reference to a list of your macro names.

If you have Excel 2000 the Sheet Change Event is fired by Data Validation.

Right click on the Sheet name tab and select "View Code" then paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$D$5" Then
Run Target.Text
End If
End Sub

Change D5 to suit you validation address.

If you have Excel 97 you can still get around this by simply placing =D5 in any cell


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Precedents.Address = "$D$5" Then
Run Target.Text
End If
End Sub


2 Combobox.

Place a comobox from the "Control Toolbox" on your sheet. Set the RowSource Property to a range that has your list of macro names. Then set the TakeFocusOnClick Property to False. Now use this code

Private Sub ComboBox1_Change()
Dim sMacro As String

If ComboBox1.ListIndex > -1 Then
sMacro = ComboBox1
Run ComboBox1
End If

End Sub

Dave


OzGrid Business Applications

Posted by Tee on May 09, 2001 9:08 PM

Hi Dave

I can't find the RowSource Pty or the TakeFocusOn etc.

I am using a combo box from the Control Toolbox in Excel 97.

Thanks once again

Tee.


Posted by Dave Hawley on May 10, 2001 5:12 AM

Sorry Tee, my mistake.

You would use the ListFillRange and forget about the TakeFocusOnClick I had CommandButtons on the brain.

Dave
OzGrid Business Applications

Posted by tee on May 10, 2001 2:22 PM

You would use the ListFillRange and forget about the TakeFocusOnClick I had CommandButtons on the brain. Dave