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

mdennis624

New Member
Joined
Feb 17, 2002
Messages
16
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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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.

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
 
Upvote 0
'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
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top