Menumaker.xls Create Macro on User Toolbar

[Bruce Roberson]

I have a hidden workbook that comes up each time I start
the Excel 2000 program with a toolbar attached to it
called Bruce's tools. This custom toolbar contains a few
specialized shortcuts that I use on a regular basis.

Apart from this, I use a variation of J Walkenbach's
menumaker.xls in which I may have two or more open
workbooks at any given time that have customized menu
trees. If one workbook is closed, then the menu tree goes
away by the very nature of design in menumaker.xls.

I don't really wish to alter his work because I want it to
work properly in all my cases. What I want to do is to add
a button to my toolbar that would call up the Create Menu
macro in the Active Workbook. Right now if I close a
workbook containing the before_close method that deletes
the menu, then if I switch to another workbook that also
has its custom macro call "Create Menu" , then I have to
go to its worksheet labeled Menusheet and click on the
button called "Create Menu" which calls up the code listed
below. The Menusheet worksheet in the active workbook also
contains a menu tree specific to the that workbook's
needs. There may be several workbooks open at a time that
contain a sheet named Menusheet with customized menutrees
for that particular workbook.

What I want to have the toolbar item do is to take the
place of having to go to the active workbook's sheet named
Menusheet and click on the button named "Create Menu".

This macro code found in menumaker.xls I am speaking of is
listed below if you need to refer to it. I don't wish to
change that code itself because I want it to work as well
in the past as it has when I open any given workbook and
it automatically does it thing when opened. This
workaround is just a shortcut to reestablish the menu tree
for the active workbook whenever a previous workbook being
closed has also removed the customized menu tree for that
particular workbook.


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

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Location for menu data
Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
''''''''''''''''''''''''''''''''''''''''''''''''''''

' Make sure the menus aren't duplicated
Call DeleteMenu

' Initialize the row counter
Row = 2

' Add the menus, menu items and submenu items using
' data stored on MenuSheet

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
' Add the top-level menu to the Worksheet
CommandBar
Set MenuObject = Application.CommandBars
(1). _
Controls.Add(Type:=msoControlPopup, _
Before:=PositionOrMacro, _
temporary:=True)
MenuObject.Caption = Caption

Case 2 ' A Menu Item
If NextLevel = 3 Then
Set MenuItem = MenuObject.Controls.Add
(Type:=msoControlPopup)
Else
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
Loop
End Sub