Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
John Walkenbach's MenuMaker Error
I created a "macros.xls" file that defines a few dozen macros for use by our
workgroup. On a hidden and locked sheet of the workbook is a custom menu for use by John Walkenbach's MenuMaker program. The file is maintained on the network, and each user copies it to his XLStart folder. Last week, a user was attempting to log off to catch a train, his computer froze, and so he flipped the switch rather than his normal Log Off procedure. Today, the macros file no longer automatically loaded. If loaded manually, it triggered an error in the following CreateMenu() code on the line: MenuItem.OnAction = PositionOrMacro under Case 2. I deleted his macros.xls, and recopied it from the network with no change. I then moved the file to a new folder, and configured Excel under the Tools, Options, <General Tab to load all files from the folder on startup. Under this config, the file loads normally. Does anyone have any ideas as to why it won't load and operate properly from the XLStart folder? 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, _ Befo=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) ' ****** ERROR TRIGGERS HERE ******** 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 End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing text case in entire database column from JOHN to John | Excel Discussion (Misc queries) | |||
how to format Doe,John to Doe, John (space after a comma) | Excel Worksheet Functions | |||
Menumaker problem | Excel Programming | |||
menumaker.xls | Excel Programming | |||
Menumaker.xls Create Macro on User Toolbar | Excel Programming |