View Full Version : [SOLVED:] make a macro menu in excel

07-08-2004, 01:26 AM
How is the best way to do this:

I have a list of 12 macro's that i want to be available to everyone. I have 10 people in my team and ideally want them located in a menu in excel (like the file,edit etc menus). Rather than going to peoples computer and making them each a menu, is there a way to open Excel and have them there automatically. At present all macros are located in a workbook but ideally id like the workbook (mymacros.xls) to be hidden from view in Excel.

Zack Barresse
07-08-2004, 02:04 AM
How about making a custom add-in (.xls)? I've got a file at work that makes one for you how you want it set-up. Can post tomorrow if you'd like.

07-08-2004, 02:17 AM
Excellent that would be great.

The only problem is i was hoping to get cracking today. I dont suppose you know where i could find one on the web?

If not if you could send me it tomorrow it would be fantastic


Zack Barresse
07-08-2004, 02:46 AM
Here's a link to part of what my file uses: http://j-walk.com/ss/excel/tips/tip53.htm

Designed by John Walkenbach, it works wonders. I like to add the faceID's to mine. You can find a nice little add-in for those on the same site.

Full directions should be at the site, else search MSKB for Creating an Add-in. Let me know if you still want that sample file. Just post back for any help. :)

07-08-2004, 07:45 AM
Hi Bartoni,

I have an add-in that I distributed to my co-workers, which when installed creates a menu on the main menu bar. I have it in the "ThisWorkbook" code pane of the addin .xla file. The uninstall code removes the menu item when the add-in is uninstalled. This way my co-workers only have to install the add-in, and the rest is taken care of! Hope this helps you with a start.

Private Sub Workbook_AddinInstall()
Dim WMB As CommandBar, WMBbp As CommandBarControl
'If menu currently exists, delete it
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("PW").Delete
On Error GoTo 0
'Set variables
Set WMB = Application.CommandBars("Worksheet Menu Bar")
Set WMBbp = WMB.Controls.Add(Type:=msoControlPopup, Before:=WMB.Controls.Count)
'Add "PW" menu to worksheet menu bar, & means alt-hotkey (underlined)
With WMBbp
.Caption = "&PW"
.Tag = "PW"
End With
'Add individual menu items and assign macros to them
With WMBbp.Controls.Add
.Caption = "Active&Workbook"
.OnAction = ThisWorkbook.Name & "!vpwbook"
End With
With WMBbp.Controls.Add
.Caption = "Active&Sheet"
.OnAction = ThisWorkbook.Name & "!vpwsheet"
End With
With WMBbp.Controls.Add
.Caption = "Specified Workbook"
.OnAction = ThisWorkbook.Name & "!vpwabook"
End With
End Sub

Private Sub Workbook_AddinUninstall()
'Delete menu item upon uninstallation of add-in
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("PW").Delete
End Sub

Zack Barresse
07-08-2004, 08:30 AM
The code basically says ( found here (http://www.vbaexpress.com/forum/showthread.php?p=2787#post2787) ) says...

Option Explicit
Sub CreateMenu()
' This sub should be executed when the workbook is opened.
' NOTE: There is no error handling in this subroutine
Dim MenuSheet As Worksheet
Dim MenuObject As CommandBarPopup
Dim MenuItem As Object
Dim SubMenuItem As CommandBarButton
Dim Row As Integer
Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId
Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
Call DeleteMenu
Row = 2
Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
MenuLevel = .Cells(Row, 1)
Caption = .Cells(Row, 2)
PositionOrMacro = .Cells(Row, 3)
Divider = .Cells(Row, 4)
FaceId = .Cells(Row, 5)
NextLevel = .Cells(Row + 1, 1)
End With
Select Case MenuLevel
Case 1 ' A Menu
Set MenuObject = Application.CommandBars(1). _
Controls.Add(Type:=msoControlPopup, _
Before:=PositionOrMacro, _
MenuObject.Caption = Caption
Case 2 ' A Menu Item
If NextLevel = 3 Then
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrMacro
End If
MenuItem.Caption = Caption
If FaceId <> "" Then MenuItem.FaceId = FaceId
If Divider Then MenuItem.BeginGroup = True
Case 3 ' A SubMenu Item
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = Caption
SubMenuItem.OnAction = PositionOrMacro
If FaceId <> "" Then SubMenuItem.FaceId = FaceId
If Divider Then SubMenuItem.BeginGroup = True
End Select
Row = Row + 1
End Sub

Sub DeleteMenu()
Dim MenuSheet As Worksheet
Dim Row As Integer
Dim Caption As String
On Error Resume Next
Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
Row = 2
Do Until IsEmpty(MenuSheet.Cells(Row, 1))
If MenuSheet.Cells(Row, 1) = 1 Then
Caption = MenuSheet.Cells(Row, 2)
End If
Row = Row + 1
On Error Goto 0
End Sub

That's a good post, and you may want to think about reading it. A lot of good input. :)

07-09-2004, 02:33 AM

If you wouldnt mind would you be able to post/send me the addin. Many Thanks!

Zack Barresse
07-09-2004, 08:50 AM
Not a problem! In the attached file (zipped), the macros I put in my Personal.xls file. I haven't had much time to mess around with it, so that's why they're there. And it's just a regular 'ole .xls right now. You'll need to save it as a .xla. This way you can change all your formatting, faceID's, macros, positions, etc. It's J-Walk's work, I just utilized it. Hope you can too. :)