Hi all,
Hopefully someone can help. I have the following code (part of a bigger piece of work) and until I started using 365 worked fine. Now I receive a runtime 13 error and I cant for the life of me figure it out other than it may be something to do with the adding a menu bar item or commandbar issue as it may have been demised but I dont know how to fix it!!
The code where the error is generated is shown below in red
All the tabs referenced are in place and triple checked
The full code is as follows:
Thanks in advance
Hopefully someone can help. I have the following code (part of a bigger piece of work) and until I started using 365 worked fine. Now I receive a runtime 13 error and I cant for the life of me figure it out other than it may be something to do with the adding a menu bar item or commandbar issue as it may have been demised but I dont know how to fix it!!
The code where the error is generated is shown below in red
All the tabs referenced are in place and triple checked
The full code is as follows:
Rich (BB 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 SubThanks in advance
Last edited by a moderator:
. While you're here.....any idea how to show text from the UI Editor without it taking it AS coding for the website??
