Add top level menu to Command Bar

scotts

New Member
Joined
Jun 26, 2010
Messages
42
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>
Code:
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Just in case its needed.....MenuSheet reads as follows:
[TABLE="width: 493"]
<colgroup><col><col><col><col><col></colgroup><tbody></tbody>[/TABLE]
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
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,314
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top