Run time error '9' Subscript out of range

[Tina]

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.CommandButton.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.Parent.name).CodeModule
**** error here

.InsertLines .CreateEventProc("Click", OLEObj.name) + 1,
"Msgbox ""You Clicked The Button"" "

End With

Next i

 

[Chip Pearson]

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.Parent.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 (e-mail address removed)