Run code like this

Sub CCCC()
Dim vbc As Object
Dim sh As Worksheet
For Each vbc In ThisWorkbook.VBProject.VBComponents
Debug.Print vbc.Name
Next
For Each sh In ThisWorkbook.Worksheets
Debug.Print sh.Name, sh.CodeName
Next

End Sub

You will see the the index into the vbcomponents collection is the Codename
of the sheet, not the Name of the sheet.

--
Regards,
Tom Ogilvy


"Steph" wrote in message
...
Hello. I have some code that inserts a click event onto Sheet 1. I was
trying to make Sheet 1 a variable, as the sheets I will be adding the
event
to will vary in name from one file to another. So, I tried to make the
sheet name a variable, and then reference the variable in the code. But
I'm
getting a subscript out of range error. My code is below...any ideas what
I'm doing wrong?

Sub Add_Click_Event()

Dim CkBox As OLEObject

SupName = ActiveSheet.Name

Set CkBox = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Check Box.1", _
Link:=False, DisplayAsIcon:=False, Left:=204.75, _
Top:=39.75, Width:=105.75, Height:=20.25)
CkBox.Name = "NewCheckBox"
CkBox.Object.Caption = "Click Me"

With ThisWorkbook.VBProject.VBComponents(SupName).CodeM odule
.AddFromString _
"Private Sub NewCheckBox_Click()" & vbCrLf & _
"Msgbox ""You clicked the box"" " & vbCrLf & _
"End Sub"
End With
End Sub