Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
On Click Event?, and how to use | Excel Programming | |||
Running command button click event code | Excel Programming | |||
Before Right Click event | Excel Programming | |||
Click Event | Excel Programming | |||
Mouse Click Event | Excel Programming |