Drop down menu for running macros

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

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.

Sorry Tee, my mistake.

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

Dave
OzGrid Business Applications

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