MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Combo Box

Posted by Garry on May 06, 2001 9:02 PM

Is it possible to activate one of a series of macros depending upon the options selcted froma Combo Box. I have tried Worksheet.SectionChange but just cant seem to get macro activated.

Posted by Dave Hawley on May 06, 2001 9:22 PM

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.


OzGrid Business Applications

Posted by Garry on May 06, 2001 10:41 PM

Hi Dave,

Thanks for the help


Posted by Ivan Moala on May 07, 2001 6:01 AM

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


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)
End With
End If
Next VBComp

Set VBCodeMod = Nothing

End Sub