List macros for user to select...

  • Thread starter Thread starter ali
  • Start date Start date
A

ali

Hi,

I am trying to create a macro that will bring up a list of 4 macros
allowing the user to select one of them from the list. On clicking o
the macro is run.

Unfortunately i am at a complete loss as to how to do this. Can anyon
please help with this?

Also, is it possible to allow the user to select more than one macr
and on clicking ok the macros are run (macro 1 first, followed by macr
2)

Many thanks for your help on this topi
 
ali, the macro below will debug.print a list of macros and which module
they are found in. You can use this list to populate a combobox (I
assume that you already know how to do that, right?)


Sub CreateListofMacros()
Attribute Macro2.VB_Description = "12/28/03 by Pete"
Attribute Macro2.VB_ProcData.VB_Invoke_Func = " \n14"
'
' Macro2 Macro
'
Dim startLine As Long, LastLine As Long, startcol As Long
Set thisProject = Application.VBE.ActiveVBProject

LineCount = Application.VBE.CodePanes(2).CodeModule.CountOfLines

'thiscomponent =
Application.VBE.ActiveVBProject.VBComponents(6).Name

For Each Pane In Application.VBE.CodePanes
startLine = 1
startcol = 1
LastLine = Pane.CodeModule.CountOfLines
SubHere = Pane.CodeModule.Find("Sub", startLine, startcol,
2000, 1, False, False)
Do While startLine < LastLine
thisSub = Pane.CodeModule.procofline(startLine, 1)
If thisSub <> thatSub Then
Debug.Print thisSub & " is found in " &
Pane.CodeModule.Name
thatSub = thisSub
End If
'On Error Resume Next

startLine = startLine + 1
Loop
Next Pane

'VBAProject.Test.CreateListofMacros

End Sub
 
Create a userform (UserForm1) with a Listbox (ListBox1) and a button
(CommandButton1)

In the userform code area, paste in:

Private Sub CommandButton1_Click()
Dim i As Integer
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) = True Then
Application.Run .List(i, 0)
End If
Next i
End With
End Sub

Private Sub UserForm_Initialize()
With Me.ListBox1
.AddItem "Macro1"
.AddItem "Macro2"
.AddItem "Macro3"
.AddItem "Macro4"
End With
End Sub

change the macro names to suit.
 
Have you considered using ALT+F8 for macros in an open workbook (not in
an add-in)?

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top