Creating a menu for macros in Excel 2003

I have used the embedded macro menu to organize some macros - this only works only in 2007, how can I get it to work in 2003?  I am open to using another menu system to organize the macros.
JP My-Add-in-Macro-Menu-all-workboo.xlam
Who is Participating?
Eric ZwiekhorstSAP Business ConsultantCommented:
The macro will run when opening the workbook.
It will create a menu sheet with all possible icons for the button.

Hope it will help you out.


Eric ZwiekhorstSAP Business ConsultantCommented:
Dear Easycapital, I use it like this in Excel 2003.


Private Sub Workbook_Open()
 With Application
         .Calculation = xlAutomatic
        .MaxChange = 0.001
 '  End With
  With Application.CommandBars(1).FindControl(, ToolsMenuID) _
  '   .Controls.Add(msoControlButton)
    .Caption = MenuCaption
 .OnAction = ThisWorkbook.Name & "!" & MacroName
  'End With
End Sub

Private Sub DeleteMenuItem()
  Dim ToolsMenu As CommandBarControl
  On Error Resume Next
  Application.CommandBars(1).FindControl(, ToolsMenuID) _
  On Error GoTo 0
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 ' DeleteMenuItem
 On Error Resume Next
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Call UnHookMouse
'nonoH = True
End Sub
Eric ZwiekhorstSAP Business ConsultantCommented:
or create a manu like this
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("Formatting"). _
                    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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

easycapitalAuthor Commented:
Zwiekhorst: Thank you for sharing.  I was not able to run them. I placed them under "Module" in the VBA editor section, but I was not able to set it up right.

Could you either embed a file with the suggested code, or take a look at the two files I created with your code?
JP Menu-1-approach.xls Menu-2-approach.xls
Eric ZwiekhorstSAP Business ConsultantCommented:
You can umbed them in the workbook macro

please look at the file that I upload

easycapitalAuthor Commented:
I want to apologize in advance.  What does the macro do?  I open the file in 2003 and nothing happens.  Could you explain me a little more?
Here you go!

Tested and it works.

Hope is what you're looking for.
easycapitalAuthor Commented:
Real nice!
Eric and yuppydu - Thanks.
Yuppydu - I am using your download right now.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.