Add top level menu to Command Bar

[scotts]

Hi

Im pretty new to excel and have inherited a piece of code to allow me to add an item to the menubar.

Issue is Im getting a "runtime error 9" when i step through the code.

Despite looking online and on here and trying a number of tings I just dont know enough about VBA to fix this, so any help would be hugely appreciated!!

Im on Windows 7 ON PC, and the sub reads as follows:

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff }p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #008f00 ; background-color: #ffffff }p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff }p.p5 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #fffb00}span.s1 {color: #011993 }span.s2 {color: #000000 }span.s3 {color: #008f00 }span.s4 {background-color: #ffffff }span.s5 {background-color: #fffb00}</style>

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


    '   Adding the Presentation entry to the Menu
'    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
'    MenuItem.OnAction = "Presentation"
'    MenuItem.Caption = "Instant Report"
'    MenuItem.FaceId = 6980
    '   Adding the Question Maintenance entry to the Menu
    'Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
    '    MenuItem.OnAction = "Category_11"
    '    MenuItem.Caption = "&Questions"
    '    MenuItem.FaceId = 2985
'    ActiveWorkbook.Protect Password:=Wbklock
End Sub

 

[scotts]

Just in case its needed.....MenuSheet reads as follows:

<colgroup><col><col><col><col><col></colgroup><tbody></tbody>

A B C D E
1 Caption Position/Macro Divider FaceID
2 1 POAP Processing 11
3 2 Update Chart resolve 2014
4 2 Configuration Settings Config_Var
5 True