Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Run time error '9' Subscript out of range
Hi,
I am getting an error in the following code at the line marked with ****. Any ideas why?? ----------------------- Workbooks("Game").Sheets("Data").Activate Dim OLEObj As OLEObject For i = 0 To 4 Set OLEObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1", Link:=False _ , DisplayAsIcon:=False, Left:=50 + i * 100, Top:=10, Width:=80, Height:= _ 25) OLEObj.name = "TheButton" & i ' ' Create the event procedure ' With ActiveWorkbook.VBProject.VBComponents(OLEObj.Paren t.name).CodeModule **** error here .InsertLines .CreateEventProc("Click", OLEObj.name) + 1, "Msgbox ""You Clicked The Button"" " End With Next i ---------------- TIA! Tina |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Run time error '9' Subscript out of range
Tina,
Your error is occuring because the worksheet has been renamed. VBA doesn't see worksheet names when looking in the VBComponents collection. Instead, it sees worksheets as named with their CodeName. Change With ActiveWorkbook.VBProject.VBComponents(OLEObj.Paren t.name).CodeModule to With ActiveWorkbook.VBProject.VBComponents _ (OLEObj.Parent.CodeName).CodeModule Initially, the Name and CodeName of a worksheet are the same, but if you rename the sheet, the Name changes but the CodeName remains the same. -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Tina" wrote in message om... Hi, I am getting an error in the following code at the line marked with ****. Any ideas why?? ----------------------- Workbooks("Game").Sheets("Data").Activate Dim OLEObj As OLEObject For i = 0 To 4 Set OLEObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1", Link:=False _ , DisplayAsIcon:=False, Left:=50 + i * 100, Top:=10, Width:=80, Height:= _ 25) OLEObj.name = "TheButton" & i ' ' Create the event procedure ' With ActiveWorkbook.VBProject.VBComponents(OLEObj.Paren t.name).CodeModule **** error here .InsertLines .CreateEventProc("Click", OLEObj.name) + 1, "Msgbox ""You Clicked The Button"" " End With Next i ---------------- TIA! Tina |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run-time error '9': subscript out of range | Excel Discussion (Misc queries) | |||
Error:Subscript out of range | Excel Discussion (Misc queries) | |||
Run Time Error 9 (Subscript out of Range) for XLA file | Excel Discussion (Misc queries) | |||
Run time error 9 : Subscript out of range | Excel Discussion (Misc queries) | |||
Run time error 9 (subscript out of range) | Excel Programming |