Solved

Menu list with Multiple Macros in Userform

Posted on 2011-02-21
9
982 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:NewToVBA
  • 5
  • 2
  • 2
9 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 34946865
Have you thought about creating a Command Bar Menu?

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
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34946952
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:

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

Open in new window


And here's the code in MODULE 1:

Enjoy!

Dave
CommandBars-r1.xlsm
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34946980
Erm, I really wouldn't recommend using CommandBarControl as a variable name! ;)
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34947036
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.

 
'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

Open in new window


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
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 34947078
@rorya - good one - better to stay away from the base names of controls.  Attached is the modified addin using CommandBarControl1 as the variable name, though I've never had problems with this - better to be safe!

Dave
CommandBars-r1.xlam
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34947291
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).
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

Open in new window

See attached,

Dave
MacroSheetOOptions-r1.xlsm
0
 

Author Closing Comment

by:NewToVBA
ID: 34947397
Amaizing! Fanstatic solutions...
0
 

Author Comment

by:NewToVBA
ID: 34947421
Thank you very much Dave and Rorya!  I am very impressed with your skills...
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34947464
All the credit is Dave's - I didn't do a thing! :)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now