List and run optional macros from a combo box or pop-up?

On 2002-02-19 15:29, mdennis624 wrote:
I have a spreadsheet with multiple control buttons used to execute several optional macro functions and sort routines. Instead of cluttering up the spreadsheet with a lot of buttons, is there a way to list all of optional macros in a List or Combo Box? The idea is to have one cell or button location with a pull down or pop-up list of macros, select the one you need, and it runs automatically.

Click to expand...

Sure! Make a list of your macros (maybe on another sheet) and name the list (say MyMacros). Then, in a cell on your main sheet, use Data Validation to create a drop-down with your list as it's contents (use =MyMacros in the List part of Data Validation). Then, put one button to the right of (or anyhere) your drop-down. Depending on what kind of button you use, you can either create another macro or put code directly in the button's click event. Say you are creating one main macro. It would look something like this:

Sub Main()
Application.Run Range("G4").Text
End Sub

(assuming that G4 is the cell with your drop-down). Make sense? If not, let me know.

-Russell

'yes it is possible.....have a look @ code;
'Note:

'using combobox from Control toolbox = ActiveX object.
'Need to reference the VBA applications extensibility file
'in your project.

'==========================================================================================
'= =
'= Module: Sheet1 =
'= Type: Document 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 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
Set VBCodeMod = Nothing
Next VBComp

End Sub


HTH

Ivan