Menu Maker macro runs on Excel XP; fails on 97

RogerC

Well-known Member
Joined
Mar 25, 2002
Messages
536
I'm attemping to use J-Walk's menu maker macro in a Workbook that will be opened on various Excel versions. It works great on my Win2000/Excel XP machine. When I open the same Workbook on several different PCs running Excel97 I get a Runtime Error 9; Subscript Out of Range error. It happens during;

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)

Can anyone give me an idea of what may be causing this?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
On 2002-08-23 15:21, RogerC wrote:
I'm attemping to use J-Walk's menu maker macro in a Workbook that will be opened on various Excel versions. It works great on my Win2000/Excel XP machine. When I open the same Workbook on several different PCs running Excel97 I get a Runtime Error 9; Subscript Out of Range error. It happens during;

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)

Can anyone give me an idea of what may be causing this?


Check the variable "PositionOrMacro"
Are you calling it in the same way ie Workbook open ??
 
Upvote 0
Ivan - Sorry, I'm not sure what you mean. I'm opening the very same Workbook file on both PCs. Wouldn't that mean it is being called the same?


Private Sub Workbook_Open()
Call CreateMenu
End Sub


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


Any help is appreciated.
 
Upvote 0
On 2002-08-23 15:50, RogerC wrote:
Ivan - Sorry, I'm not sure what you mean. I'm opening the very same Workbook file on both PCs. Wouldn't that mean it is being called the same?

RogerC, yes you are correct, what I really meant was the same way as designed ie Workbook open event and not via commandbutton as I was thinking along the lines of something concerning this control.
Did you check the variable ??
 
Upvote 0
ADVERTISEMENT
Ivan - I'm sorry, I'm not sure how to do that. Can you tell me how to check the variable "PositionOrMacro"?
 
Upvote 0
On 2002-08-23 17:04, RogerC wrote:
Ivan - I'm sorry, I'm not sure how to do that. Can you tell me how to check the variable "PositionOrMacro"?

When it errors out you should be given the option of either
End or Debug > Select Debug....the error line should be highlighted. Now just hover your mouse over the variable to see what it gives you. Otherwise just place a msgbox just before the offending line to read this eg

Msgbox positionormacro
 
Upvote 0
ADVERTISEMENT
Ivan, It says PositionOrMacro = 13. I'm not sure what to do with that.
 
Upvote 0
On 2002-08-26 12:50, RogerC wrote:
Ivan, It says PositionOrMacro = 13. I'm not sure what to do with that.

For the time being change it to 10 or what
what ever the count of you controls are on this commandbar in Excel97
 
Upvote 0

Forum statistics

Threads
1,196,254
Messages
6,014,279
Members
441,811
Latest member
Peco73267326

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