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>
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