Add click event with code
 

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

Add click event with code
 

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

Add click event with code
 

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" wrote in message
...
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

Add click event with code
 

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).

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
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" wrote in message
...
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

Add click event with code
 

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).CodeM odule

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" wrote in message
...
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).

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
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" wrote in message
...
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

Add click event with code
 

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

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
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).CodeM odule

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" wrote in message
...
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).

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
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" wrote in message
...
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

Add click event with code
 

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

Thanks again!!

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

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
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).CodeM odule

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" wrote in message
...
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).

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
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" wrote in message
...
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