Run time error '9' Subscript out of range

  • Thread starter Thread starter Tina
  • Start date Start date
T

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
 
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
Click to expand...
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)
 
Back
Top