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

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