Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

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

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    York, PA
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    '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_DropButt******* =
    '= 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_DropButt*******()
    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •