H
Huyeote
Hi all,
I try to make a Excel add-in based on one of John Walkenbach's example
My add-in will add a new menu to enable users to use some handy tool
when workbook is opened. I can load it to Excel successfully throug
Tools\Add-in menu but when I re-start Excel, I got error message sayin
"Run time error '1004' Method 'Cells' of object '_Global' failed".
list my code below. Can anyone help me to debug it?
FYI, in the add-in workbook, I list menue level and captions an
command just like John's sample workbook. I copied its URL below.
http://j-walk.com/ss/excel/files/menumakr.exe
Thanks!
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 Row As Integer
Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider
FaceId
Dim ParentMenuItem, myTag As String, ParentCell As Range
''''''''''''''''''''''''''''''''''''''''''''''''''''
' 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
MenuObject.Tag = Caption
Case Is >= 2 ' sub menu items
'use Tag property to find parent menu item to host on
'-------------following is the code havin
problem------------------------
Set ParentCell = MenuSheet.Range(Cells(2, 1)
Cells(Row, 1)) _
.Find(what:=MenuLevel - 1, LookIn:=xlValues, _
SearchDirection:=xlPrevious)
'-------------above is the code having problem------------------------
Debug.Print "ParentCell locates at "
ParentCell.Address
myTag = ParentCell.Offset(0, 1)
Debug.Print "myTag is " & myTag
Set ParentMenuItem = CommandBars.FindControl _
(Type:=msoControlPopup, Tag:=myTag)
Debug.Print "Parent Item is " & ParentMenuItem.Tag
If NextLevel = MenuLevel + 1 Then 'if this item ha
child item
Set MenuItem
ParentMenuItem.Controls.Add(Type:=msoControlPopup)
Else 'if this item is lowest level in its stream
Set MenuItem
ParentMenuItem.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrMacro
End If
MenuItem.Caption = Caption
MenuItem.Tag = Caption
If FaceId <> "" Then MenuItem.FaceId = FaceId
If Divider Then MenuItem.BeginGroup = True
End Select
Set ParentMenuItem = Nothing
Set MenuItem = Nothing
Row = Row + 1
Loop
End Su
I try to make a Excel add-in based on one of John Walkenbach's example
My add-in will add a new menu to enable users to use some handy tool
when workbook is opened. I can load it to Excel successfully throug
Tools\Add-in menu but when I re-start Excel, I got error message sayin
"Run time error '1004' Method 'Cells' of object '_Global' failed".
list my code below. Can anyone help me to debug it?
FYI, in the add-in workbook, I list menue level and captions an
command just like John's sample workbook. I copied its URL below.
http://j-walk.com/ss/excel/files/menumakr.exe
Thanks!
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 Row As Integer
Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider
FaceId
Dim ParentMenuItem, myTag As String, ParentCell As Range
''''''''''''''''''''''''''''''''''''''''''''''''''''
' 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
MenuObject.Tag = Caption
Case Is >= 2 ' sub menu items
'use Tag property to find parent menu item to host on
'-------------following is the code havin
problem------------------------
Set ParentCell = MenuSheet.Range(Cells(2, 1)
Cells(Row, 1)) _
.Find(what:=MenuLevel - 1, LookIn:=xlValues, _
SearchDirection:=xlPrevious)
'-------------above is the code having problem------------------------
Debug.Print "ParentCell locates at "
ParentCell.Address
myTag = ParentCell.Offset(0, 1)
Debug.Print "myTag is " & myTag
Set ParentMenuItem = CommandBars.FindControl _
(Type:=msoControlPopup, Tag:=myTag)
Debug.Print "Parent Item is " & ParentMenuItem.Tag
If NextLevel = MenuLevel + 1 Then 'if this item ha
child item
Set MenuItem
ParentMenuItem.Controls.Add(Type:=msoControlPopup)
Else 'if this item is lowest level in its stream
Set MenuItem
ParentMenuItem.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrMacro
End If
MenuItem.Caption = Caption
MenuItem.Tag = Caption
If FaceId <> "" Then MenuItem.FaceId = FaceId
If Divider Then MenuItem.BeginGroup = True
End Select
Set ParentMenuItem = Nothing
Set MenuItem = Nothing
Row = Row + 1
Loop
End Su