Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Method 'Cells' of object '_Global' failed
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, _ Befo=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 -- Huyeot ----------------------------------------------------------------------- Huyeote's Profile: http://www.excelforum.com/member.php...fo&userid=1389 View this thread: http://www.excelforum.com/showthread.php?threadid=27037 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Method 'Range' of object '_global' failed | Excel Discussion (Misc queries) | |||
Method 'Range' of object '_Global' failed | Excel Programming | |||
Error 1004: Method 'Cells' of object '_Global' failed | Excel Programming |