Combo Box

Hi Garry

Yes it is possible. Use a ComboBox from the "Control Toolbox" and then place in this code:

Private Sub ComboBox1_Change()
Dim sMacro As String
If ComboBox1.ListIndex > -1 Then
sMacro = ComboBox1
Run sMacro
End If
End Sub

The same result can also be achieved by using Data>Validation and setting to a List. Then placing some code in the Sheet Change event. Although if you are using Excel 97 there is a bit of a trick to this.

Dave

OzGrid Business Applications

Hi Dave,

Thanks for the help

Garry

Garry
As Dave has mentioned there are a number of other ways to do this.
But if you require the macros to be automatically
put into the combobox selection and then asked weather
to run it then this code may help.

Option Explicit
'==========================================================================================
'= =
'= Module: Sheet1 =
'= Type: Document Class Module =
'= =
'= Developer: Ivan F Moala =
'= Date: 25-Oct-2000 =
'= =
'= Description: Requires reference to Microsoft VBA for applications extensibilty =
'= =
'= Subprocedures: ComboBox1_Click =
'= ComboBox1_DropButtonClick =
'= Functions: None =
'= Properties: None =
'= DLL Declares: None =
'= =
'==========================================================================================

Private Sub ComboBox1_Click()
Dim Q As Integer
Q = MsgBox("Run [" & ComboBox1.Text & "] macro ??", vbYesNo)
If Q = vbYes Then
Application.Run ComboBox1.Text
End If
End Sub

Private Sub ComboBox1_DropButtonClick()
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim ProcName As String
Dim VBComp

ComboBox1.Clear

For Each VBComp In ThisWorkbook.VBProject.VBComponents

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(VBComp.Name).CodeModule

If VBComp.Type = vbext_ct_StdModule Then

With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
ComboBox1.AddItem .ProcOfLine(StartLine, vbext_pk_Proc)
StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
End If
Next VBComp

Set VBCodeMod = Nothing

End Sub

Ivan