Prompt User to Select Macro to Run?

  • Thread starter SeventhFloorProfessor
  • Start date
S

SeventhFloorProfessor

Is it possible to have a macro run that prompts the user to select which
macro to run? If so, how would that be coded?

The situation:

I am creating a workbook that will guide the user to create a "template"
(not as a literal file, but in the sense that this will be the starting
workbook), which the user will be prompted to save as another file name (this
will be their personal template for their class). Since I am creating this
for teachers who are not familiar with macros, I would like to store the
various macros I have written in one workbook, and then prompt the user to
select which macro to run, depending on what they want to do...

help!
 
P

Patrick Molloy

Excel 2003
menu bar example

1) cut/paste this to a standard code module.
2)call AddMenuItems
this adds "Test" as a menu item immediately left of "Help" om the
standard worksheet menu bar




Option Explicit
Sub AddMenuItems()
Dim cbMain As CommandBar
Dim ctrl As CommandBarPopup
RemoveMenuItems "Tes&t"
Set cbMain = CommandBars("Worksheet Menu Bar")
With cbMain.Controls.Add(msoControlPopup, Before:=cbMain.Controls.Count,
Temporary:=True)
.Caption = "Tes&t"
With .Controls.Add(msoControlButton)
.OnAction = "ABC"
.Caption = "ABC"
End With
With .Controls.Add(msoControlButton)
.OnAction = "DEF"
.Caption = "DEF"
End With
End With
End Sub
Sub RemoveMenuItems(sMenu As String)
Dim mn As CommandBarControl
On Error GoTo quit
Set mn = CommandBars("Worksheet Menu Bar").Controls("tes&t")
Do While Not mn Is Nothing
mn.Delete
Set mn = CommandBars("Worksheet Menu Bar").Controls("tes&t")
Loop
quit:
On Error GoTo 0
End Sub
Sub ABC()
MsgBox "ABC running"
End Sub
Sub DEF()
MsgBox "DEF running"
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top