NewToVBA
asked on
Menu list with Multiple Macros in Userform
Hi, I have a number of macros that would like all to be displayed on a list and let a user pick whichever they need. I guess userform with option buttons is a means to do it. I will greaetly appreciate if somebody could show me how to do it in an example.
Thank you, N.
Thank you, N.
Is this what you're looking for. I culled commandbar work from some addin work I was doing. You could even make this an addin and I'll help you with that if you're headed that direction.
For now, test and see if this is what you want. After loading, go to Addins (because that's where I put the command bar) and run the "great" macros.
Dave
Here's the code in ThisWorkbook that builds the commandbar, and deletes them on file close:
And here's the code in MODULE 1:
Enjoy!
Dave
CommandBars-r1.xlsm
For now, test and see if this is what you want. After loading, go to Addins (because that's where I put the command bar) and run the "great" macros.
Dave
Here's the code in ThisWorkbook that builds the commandbar, and deletes them on file close:
Private Sub Workbook_Open()
'Commandbar stuff
Set CommandBarMenu1 = Application.CommandBars(1).Controls.Add(Type:=msoControlPopup, temporary:=True) 'msocontrolpopup
CommandBarMenu1.Caption = "&Great macros!"
Set CommandBarControl = CommandBarMenu1.Controls.Add(Type:=msoControlButton, temporary:=True) 'msocontrolbutton
CommandBarControl.Caption = "&MyMacro_1"
CommandBarControl.OnAction = "MyMacro_1"
Set CommandBarControl = CommandBarMenu1.Controls.Add(Type:=msoControlButton, temporary:=True) 'msocontrolbutton
CommandBarControl.Caption = "&MyMacro_2"
CommandBarControl.OnAction = "MyMacro_2"
Set CommandBarControl = CommandBarMenu1.Controls.Add(Type:=msoControlButton, temporary:=True) 'msocontrolbutton
CommandBarControl.Caption = "E&xit Great macros!"
CommandBarControl.OnAction = "MyGreatMacro_Terminate"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call MyGreatMacro_Terminate
End Sub
And here's the code in MODULE 1:
Enjoy!
Dave
CommandBars-r1.xlsm
Erm, I really wouldn't recommend using CommandBarControl as a variable name! ;)
And here it is as an add-in:
Note there's an option to work in debug mode (just set cDebug = true somewhere before the code runs). if you run the macro tempUnmakeMacro, it tells Excel its not an addin and turns cDebug on. So, if you hit the EXIT, the file closes (as opposed to unloading the addin)... Anyway, perhaps more than you need, but just in case, this is how I do it.
Exiting the Addin, takes it off the Users's INSTALLED list, if they installed via Excel Options. They can add it back, if they want to ressurrect it, otherwise its always there for them.
Copy the addin to the Addins folder, then you can add it via Excel Options. Or just double-click on the addin from whereever you save it to load. My users do it both ways.
Note, I moved my macros to a MODULE, and left the addin handling in a module named as such.
Feel free to ask questions. It would be pretty easy to keep adding macros to your menubar from here...
Enjoy!
Dave
CommandBars-r1.xlam
Note there's an option to work in debug mode (just set cDebug = true somewhere before the code runs). if you run the macro tempUnmakeMacro, it tells Excel its not an addin and turns cDebug on. So, if you hit the EXIT, the file closes (as opposed to unloading the addin)... Anyway, perhaps more than you need, but just in case, this is how I do it.
Exiting the Addin, takes it off the Users's INSTALLED list, if they installed via Excel Options. They can add it back, if they want to ressurrect it, otherwise its always there for them.
Copy the addin to the Addins folder, then you can add it via Excel Options. Or just double-click on the addin from whereever you save it to load. My users do it both ways.
'in a public module, the following declarations (so you can close the menu bar later...
Public sourceWb As Variant
Public cDebug As Boolean, addInName As String
Public CommandBarMenu1 As CommandBarPopup, CommandBarMenu2 As CommandBarPopup
Public CommandBarControl As CommandBarButton
Public Sub MyGreatMacro_Terminate()
On Error Resume Next 'if exit commandbar menu, then this fires, then workbook_BeforeClose fires, so would get an error the second time around
CommandBarMenu1.Delete
If cDebug Then
Application.EnableEvents = False
ActiveWorkbook.Close
Application.EnableEvents = True
Else
AddIns(addInName).Installed = False
ThisWorkbook.Close SaveChanges:=False
End If
On Error GoTo 0
End Sub
Public Sub tempUnmakeAddin()
ThisWorkbook.IsAddin = False
End Sub
Note, I moved my macros to a MODULE, and left the addin handling in a module named as such.
Feel free to ask questions. It would be pretty easy to keep adding macros to your menubar from here...
Enjoy!
Dave
CommandBars-r1.xlam
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here's a couple more options - putting the macro names and descriptions on the sheet with a button you click to run the macro, and creating a hyperlink that is "followed" to test and run the right macro (see code in the Sheet1 codepage, as below).
Dave
MacroSheetOOptions-r1.xlsm
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Select Case Target.Name
Case "MyMacro_1":
Call MyMacro_1
Case "MyMacro_2":
Call MyMacro_2
Case Else
'do nothing
End Select
End Sub
See attached,Dave
MacroSheetOOptions-r1.xlsm
ASKER
Amaizing! Fanstatic solutions...
ASKER
Thank you very much Dave and Rorya! I am very impressed with your skills...
All the credit is Dave's - I didn't do a thing! :)
Here are some tips for creating command bars -
http://msdn.microsoft.com/en-us/library/aa141022(v=office.10).aspx
http://www.ozgrid.com/VBA/custom-menus.htm
Dave