Add click event with code

[Steph]

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.CheckBox.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).CodeModule
.AddFromString _
"Private Sub NewCheckBox_Click()" & vbCrLf & _
"Msgbox ""You clicked the box"" " & vbCrLf & _
"End Sub"
End With
End Sub

 

[Tom Ogilvy]

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.

 

[Steph]

Hi Tom, I though it might be the code name.....I tried that earlier with the
code below. Basically, the only difference with this code and the piece I
posted earlier is the line
SupName = ActiveSheet.Name changed to
SupName = ActiveSheet.CodeName

But that didn't work either?

 

[Tom Ogilvy]

What version of Excel are you using. If you are using Excel 97, then the
name of the checkbox is not NewCheckBox as might be your intention.

When you say doesn't work, what do you mean? The code errors (what is the
error message; what is highlighted) or does it run to completion, but the
event doesn't file (if xl97, see above).

 

[Steph]

Hi Tom,

I'm in Excel 2003. When I run, I get the error Subscript out of range, the
the highlighted line is
With ThisWorkbook.VBProject.VBComponents(SupName).CodeModule

I found it....Damn I'm dumb sometimes. I was running this code from a
different workbook than the active. Wihin my code, I was mixing
ActiveWorkbook and ThisWorkbook. So when I changed the above line to With
ActiveWorkbook, it worked fine.

Thanks for you time, Tom! Did I mention I love you!?

 

[Tom Ogilvy]

You still need to use the code name. If you hadn't, then I assume they
match in this case.

 

[Steph]

Yep. I switched the variable declaration from
SupName = ActiveSheet.Name to
SupName = ActiveSheet.CodeName

Thanks again!!