|
Adding Embedded OLE Objects on a Spread Sheet
Hey, this is my first posting so bear with me...
I have embedded several checkboxes on a spread sheet and i wish to write code for the change event, but i can not figure how to get to it. I created the checkboxes through code: MySheet.OLEObjects.Add ("Forms.CheckBox.1") Set myOLEObject = MySheet.OLEObjects.Item(xx) myOLEObject.Name = "wkCheck" & x myOLEObject.Activate myOLEObject.Left = ActiveCell.Left myOLEObject.Top = ActiveCell.Top myOLEObject.Height = ActiveCell.RowHeight myOLEObject.Width = ActiveCell.Width myOLEObject.Object.Caption = "" How can i write into the VBA the change event code? I have played with VBE before, but am not too strong in that area. Please help. I want to kick off a query when the check box is changed to true. Thank you, James McDowell |
Adding Embedded OLE Objects on a Spread Sheet
I also need to know how to write it into the VBA so as i add more checkboxes
as they are needed I can have the code executed. Sort of like an .OnAction command. Thank you, James McDowell "James McDowell" wrote: Hey, this is my first posting so bear with me... I have embedded several checkboxes on a spread sheet and i wish to write code for the change event, but i can not figure how to get to it. I created the checkboxes through code: MySheet.OLEObjects.Add ("Forms.CheckBox.1") Set myOLEObject = MySheet.OLEObjects.Item(xx) myOLEObject.Name = "wkCheck" & x myOLEObject.Activate myOLEObject.Left = ActiveCell.Left myOLEObject.Top = ActiveCell.Top myOLEObject.Height = ActiveCell.RowHeight myOLEObject.Width = ActiveCell.Width myOLEObject.Object.Caption = "" How can i write into the VBA the change event code? I have played with VBE before, but am not too strong in that area. Please help. I want to kick off a query when the check box is changed to true. Thank you, James McDowell |
Adding Embedded OLE Objects on a Spread Sheet
Is this pre or post my response?
-- HTH RP (remove nothere from the email address if mailing direct) "James McDowell" wrote in message ... I also need to know how to write it into the VBA so as i add more checkboxes as they are needed I can have the code executed. Sort of like an ..OnAction command. Thank you, James McDowell "James McDowell" wrote: Hey, this is my first posting so bear with me... I have embedded several checkboxes on a spread sheet and i wish to write code for the change event, but i can not figure how to get to it. I created the checkboxes through code: MySheet.OLEObjects.Add ("Forms.CheckBox.1") Set myOLEObject = MySheet.OLEObjects.Item(xx) myOLEObject.Name = "wkCheck" & x myOLEObject.Activate myOLEObject.Left = ActiveCell.Left myOLEObject.Top = ActiveCell.Top myOLEObject.Height = ActiveCell.RowHeight myOLEObject.Width = ActiveCell.Width myOLEObject.Object.Caption = "" How can i write into the VBA the change event code? I have played with VBE before, but am not too strong in that area. Please help. I want to kick off a query when the check box is changed to true. Thank you, James McDowell |
Adding Embedded OLE Objects on a Spread Sheet
It tells me that my event handler is invalid.
"Bob Phillips" wrote: Here is an example Dim StartLine As Long With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule StartLine = .CreateEventProc("Click", "wkCheck1") + 1 .InsertLines StartLine, _ "Dim ans" & vbCrLf & _ " ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _ " If ans = vbNo Then MsgBox ans" End With -- HTH RP (remove nothere from the email address if mailing direct) "James McDowell" <James wrote in message ... Hey, this is my first posting so bear with me... I have embedded several checkboxes on a spread sheet and i wish to write code for the change event, but i can not figure how to get to it. I created the checkboxes through code: MySheet.OLEObjects.Add ("Forms.CheckBox.1") Set myOLEObject = MySheet.OLEObjects.Item(xx) myOLEObject.Name = "wkCheck" & x myOLEObject.Activate myOLEObject.Left = ActiveCell.Left myOLEObject.Top = ActiveCell.Top myOLEObject.Height = ActiveCell.RowHeight myOLEObject.Width = ActiveCell.Width myOLEObject.Object.Caption = "" How can i write into the VBA the change event code? I have played with VBE before, but am not too strong in that area. Please help. I want to kick off a query when the check box is changed to true. Thank you, James McDowell |
Adding Embedded OLE Objects on a Spread Sheet
OK, I made a change in the code provided and switched out "wkCheck1" with
"CheckBox1" and it worked. Eventhough in the code it changed the objects name, it is not recognizing the assigned name. Any hints on this??? "Bob Phillips" wrote: Here is an example Dim StartLine As Long With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule StartLine = .CreateEventProc("Click", "wkCheck1") + 1 .InsertLines StartLine, _ "Dim ans" & vbCrLf & _ " ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _ " If ans = vbNo Then MsgBox ans" End With -- HTH RP (remove nothere from the email address if mailing direct) "James McDowell" <James wrote in message ... Hey, this is my first posting so bear with me... I have embedded several checkboxes on a spread sheet and i wish to write code for the change event, but i can not figure how to get to it. I created the checkboxes through code: MySheet.OLEObjects.Add ("Forms.CheckBox.1") Set myOLEObject = MySheet.OLEObjects.Item(xx) myOLEObject.Name = "wkCheck" & x myOLEObject.Activate myOLEObject.Left = ActiveCell.Left myOLEObject.Top = ActiveCell.Top myOLEObject.Height = ActiveCell.RowHeight myOLEObject.Width = ActiveCell.Width myOLEObject.Object.Caption = "" How can i write into the VBA the change event code? I have played with VBE before, but am not too strong in that area. Please help. I want to kick off a query when the check box is changed to true. Thank you, James McDowell |
Adding Embedded OLE Objects on a Spread Sheet
OK, I changed "wkCheck1" to "CHeckBox1" and it worked. Why will it not
reconize the name that i have given it in the code. If I go into design mode and look at the properties, under the mane, it still shows CheckBox1 "Bob Phillips" wrote: Here is an example Dim StartLine As Long With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule StartLine = .CreateEventProc("Click", "wkCheck1") + 1 .InsertLines StartLine, _ "Dim ans" & vbCrLf & _ " ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _ " If ans = vbNo Then MsgBox ans" End With -- HTH RP (remove nothere from the email address if mailing direct) "James McDowell" <James wrote in message ... Hey, this is my first posting so bear with me... I have embedded several checkboxes on a spread sheet and i wish to write code for the change event, but i can not figure how to get to it. I created the checkboxes through code: MySheet.OLEObjects.Add ("Forms.CheckBox.1") Set myOLEObject = MySheet.OLEObjects.Item(xx) myOLEObject.Name = "wkCheck" & x myOLEObject.Activate myOLEObject.Left = ActiveCell.Left myOLEObject.Top = ActiveCell.Top myOLEObject.Height = ActiveCell.RowHeight myOLEObject.Width = ActiveCell.Width myOLEObject.Object.Caption = "" How can i write into the VBA the change event code? I have played with VBE before, but am not too strong in that area. Please help. I want to kick off a query when the check box is changed to true. Thank you, James McDowell |
Adding Embedded OLE Objects on a Spread Sheet
James,
The names have to align to what you have created. I use wkCheck1, so your create code needs to use that name (or vice versa). -- HTH RP (remove nothere from the email address if mailing direct) "James McDowell" wrote in message ... It tells me that my event handler is invalid. "Bob Phillips" wrote: Here is an example Dim StartLine As Long With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule StartLine = .CreateEventProc("Click", "wkCheck1") + 1 .InsertLines StartLine, _ "Dim ans" & vbCrLf & _ " ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _ " If ans = vbNo Then MsgBox ans" End With -- HTH RP (remove nothere from the email address if mailing direct) "James McDowell" <James wrote in message ... Hey, this is my first posting so bear with me... I have embedded several checkboxes on a spread sheet and i wish to write code for the change event, but i can not figure how to get to it. I created the checkboxes through code: MySheet.OLEObjects.Add ("Forms.CheckBox.1") Set myOLEObject = MySheet.OLEObjects.Item(xx) myOLEObject.Name = "wkCheck" & x myOLEObject.Activate myOLEObject.Left = ActiveCell.Left myOLEObject.Top = ActiveCell.Top myOLEObject.Height = ActiveCell.RowHeight myOLEObject.Width = ActiveCell.Width myOLEObject.Object.Caption = "" How can i write into the VBA the change event code? I have played with VBE before, but am not too strong in that area. Please help. I want to kick off a query when the check box is changed to true. Thank you, James McDowell |
Adding Embedded OLE Objects on a Spread Sheet
That's odd James, as I ran your code and it worked.
Oops, I forgot, I got an error so I amended it to Set MySheet = ActiveSheet Set myOLEObject = MySheet.OLEObjects.Add("Forms.CheckBox.1") 'Set myOLEObject = MySheet.OLEObjects.Item(xx) myOLEObject.Name = "wkCheck" & x myOLEObject.Activate myOLEObject.Left = ActiveCell.Left myOLEObject.Top = ActiveCell.Top myOLEObject.Height = ActiveCell.RowHeight myOLEObject.Width = ActiveCell.Width myOLEObject.Object.Caption = "" -- HTH RP (remove nothere from the email address if mailing direct) "James McDowell" wrote in message ... OK, I changed "wkCheck1" to "CHeckBox1" and it worked. Why will it not reconize the name that i have given it in the code. If I go into design mode and look at the properties, under the mane, it still shows CheckBox1 "Bob Phillips" wrote: Here is an example Dim StartLine As Long With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule StartLine = .CreateEventProc("Click", "wkCheck1") + 1 .InsertLines StartLine, _ "Dim ans" & vbCrLf & _ " ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _ " If ans = vbNo Then MsgBox ans" End With -- HTH RP (remove nothere from the email address if mailing direct) "James McDowell" <James wrote in message ... Hey, this is my first posting so bear with me... I have embedded several checkboxes on a spread sheet and i wish to write code for the change event, but i can not figure how to get to it. I created the checkboxes through code: MySheet.OLEObjects.Add ("Forms.CheckBox.1") Set myOLEObject = MySheet.OLEObjects.Item(xx) myOLEObject.Name = "wkCheck" & x myOLEObject.Activate myOLEObject.Left = ActiveCell.Left myOLEObject.Top = ActiveCell.Top myOLEObject.Height = ActiveCell.RowHeight myOLEObject.Width = ActiveCell.Width myOLEObject.Object.Caption = "" How can i write into the VBA the change event code? I have played with VBE before, but am not too strong in that area. Please help. I want to kick off a query when the check box is changed to true. Thank you, James McDowell |
Adding Embedded OLE Objects on a Spread Sheet
I made the change, but the code will still not acknowledge the new name
created. I have tried myOLEObject as an OBJECT and as a OLEOBJECT to no avail. I have a way around it, I just want it to be a clean loop so i can reference all of the objects in the same loop. Thank you for your help!!! "Bob Phillips" wrote: That's odd James, as I ran your code and it worked. Oops, I forgot, I got an error so I amended it to Set MySheet = ActiveSheet Set myOLEObject = MySheet.OLEObjects.Add("Forms.CheckBox.1") 'Set myOLEObject = MySheet.OLEObjects.Item(xx) myOLEObject.Name = "wkCheck" & x myOLEObject.Activate myOLEObject.Left = ActiveCell.Left myOLEObject.Top = ActiveCell.Top myOLEObject.Height = ActiveCell.RowHeight myOLEObject.Width = ActiveCell.Width myOLEObject.Object.Caption = "" -- HTH RP (remove nothere from the email address if mailing direct) "James McDowell" wrote in message ... OK, I changed "wkCheck1" to "CHeckBox1" and it worked. Why will it not reconize the name that i have given it in the code. If I go into design mode and look at the properties, under the mane, it still shows CheckBox1 "Bob Phillips" wrote: Here is an example Dim StartLine As Long With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule StartLine = .CreateEventProc("Click", "wkCheck1") + 1 .InsertLines StartLine, _ "Dim ans" & vbCrLf & _ " ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _ " If ans = vbNo Then MsgBox ans" End With -- HTH RP (remove nothere from the email address if mailing direct) "James McDowell" <James wrote in message ... Hey, this is my first posting so bear with me... I have embedded several checkboxes on a spread sheet and i wish to write code for the change event, but i can not figure how to get to it. I created the checkboxes through code: MySheet.OLEObjects.Add ("Forms.CheckBox.1") Set myOLEObject = MySheet.OLEObjects.Item(xx) myOLEObject.Name = "wkCheck" & x myOLEObject.Activate myOLEObject.Left = ActiveCell.Left myOLEObject.Top = ActiveCell.Top myOLEObject.Height = ActiveCell.RowHeight myOLEObject.Width = ActiveCell.Width myOLEObject.Object.Caption = "" How can i write into the VBA the change event code? I have played with VBE before, but am not too strong in that area. Please help. I want to kick off a query when the check box is changed to true. Thank you, James McDowell |
Adding Embedded OLE Objects on a Spread Sheet
James,
Can you send me your workbook so I can take a look? -- HTH RP (remove nothere from the email address if mailing direct) "James McDowell" wrote in message ... I made the change, but the code will still not acknowledge the new name created. I have tried myOLEObject as an OBJECT and as a OLEOBJECT to no avail. I have a way around it, I just want it to be a clean loop so i can reference all of the objects in the same loop. Thank you for your help!!! "Bob Phillips" wrote: That's odd James, as I ran your code and it worked. Oops, I forgot, I got an error so I amended it to Set MySheet = ActiveSheet Set myOLEObject = MySheet.OLEObjects.Add("Forms.CheckBox.1") 'Set myOLEObject = MySheet.OLEObjects.Item(xx) myOLEObject.Name = "wkCheck" & x myOLEObject.Activate myOLEObject.Left = ActiveCell.Left myOLEObject.Top = ActiveCell.Top myOLEObject.Height = ActiveCell.RowHeight myOLEObject.Width = ActiveCell.Width myOLEObject.Object.Caption = "" -- HTH RP (remove nothere from the email address if mailing direct) "James McDowell" wrote in message ... OK, I changed "wkCheck1" to "CHeckBox1" and it worked. Why will it not reconize the name that i have given it in the code. If I go into design mode and look at the properties, under the mane, it still shows CheckBox1 "Bob Phillips" wrote: Here is an example Dim StartLine As Long With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule StartLine = .CreateEventProc("Click", "wkCheck1") + 1 .InsertLines StartLine, _ "Dim ans" & vbCrLf & _ " ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _ " If ans = vbNo Then MsgBox ans" End With -- HTH RP (remove nothere from the email address if mailing direct) "James McDowell" <James wrote in message ... Hey, this is my first posting so bear with me... I have embedded several checkboxes on a spread sheet and i wish to write code for the change event, but i can not figure how to get to it. I created the checkboxes through code: MySheet.OLEObjects.Add ("Forms.CheckBox.1") Set myOLEObject = MySheet.OLEObjects.Item(xx) myOLEObject.Name = "wkCheck" & x myOLEObject.Activate myOLEObject.Left = ActiveCell.Left myOLEObject.Top = ActiveCell.Top myOLEObject.Height = ActiveCell.RowHeight myOLEObject.Width = ActiveCell.Width myOLEObject.Object.Caption = "" How can i write into the VBA the change event code? I have played with VBE before, but am not too strong in that area. Please help. I want to kick off a query when the check box is changed to true. Thank you, James McDowell |
Adding Embedded OLE Objects on a Spread Sheet
Your email address is coming back as undeliverable.
"Bob Phillips" wrote: James, Can you send me your workbook so I can take a look? -- HTH RP (remove nothere from the email address if mailing direct) "James McDowell" wrote in message ... I made the change, but the code will still not acknowledge the new name created. I have tried myOLEObject as an OBJECT and as a OLEOBJECT to no avail. I have a way around it, I just want it to be a clean loop so i can reference all of the objects in the same loop. Thank you for your help!!! "Bob Phillips" wrote: That's odd James, as I ran your code and it worked. Oops, I forgot, I got an error so I amended it to Set MySheet = ActiveSheet Set myOLEObject = MySheet.OLEObjects.Add("Forms.CheckBox.1") 'Set myOLEObject = MySheet.OLEObjects.Item(xx) myOLEObject.Name = "wkCheck" & x myOLEObject.Activate myOLEObject.Left = ActiveCell.Left myOLEObject.Top = ActiveCell.Top myOLEObject.Height = ActiveCell.RowHeight myOLEObject.Width = ActiveCell.Width myOLEObject.Object.Caption = "" -- HTH RP (remove nothere from the email address if mailing direct) "James McDowell" wrote in message ... OK, I changed "wkCheck1" to "CHeckBox1" and it worked. Why will it not reconize the name that i have given it in the code. If I go into design mode and look at the properties, under the mane, it still shows CheckBox1 "Bob Phillips" wrote: Here is an example Dim StartLine As Long With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule StartLine = .CreateEventProc("Click", "wkCheck1") + 1 .InsertLines StartLine, _ "Dim ans" & vbCrLf & _ " ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _ " If ans = vbNo Then MsgBox ans" End With -- HTH RP (remove nothere from the email address if mailing direct) "James McDowell" <James wrote in message ... Hey, this is my first posting so bear with me... I have embedded several checkboxes on a spread sheet and i wish to write code for the change event, but i can not figure how to get to it. I created the checkboxes through code: MySheet.OLEObjects.Add ("Forms.CheckBox.1") Set myOLEObject = MySheet.OLEObjects.Item(xx) myOLEObject.Name = "wkCheck" & x myOLEObject.Activate myOLEObject.Left = ActiveCell.Left myOLEObject.Top = ActiveCell.Top myOLEObject.Height = ActiveCell.RowHeight myOLEObject.Width = ActiveCell.Width myOLEObject.Object.Caption = "" How can i write into the VBA the change event code? I have played with VBE before, but am not too strong in that area. Please help. I want to kick off a query when the check box is changed to true. Thank you, James McDowell |
Adding Embedded OLE Objects on a Spread Sheet
bob dot phillips at tiscali dot co dot uk
do the obvious with that. Regards Bob "James McDowell" wrote in message ... Your email address is coming back as undeliverable. "Bob Phillips" wrote: James, Can you send me your workbook so I can take a look? -- HTH RP (remove nothere from the email address if mailing direct) "James McDowell" wrote in message ... I made the change, but the code will still not acknowledge the new name created. I have tried myOLEObject as an OBJECT and as a OLEOBJECT to no avail. I have a way around it, I just want it to be a clean loop so i can reference all of the objects in the same loop. Thank you for your help!!! "Bob Phillips" wrote: That's odd James, as I ran your code and it worked. Oops, I forgot, I got an error so I amended it to Set MySheet = ActiveSheet Set myOLEObject = MySheet.OLEObjects.Add("Forms.CheckBox.1") 'Set myOLEObject = MySheet.OLEObjects.Item(xx) myOLEObject.Name = "wkCheck" & x myOLEObject.Activate myOLEObject.Left = ActiveCell.Left myOLEObject.Top = ActiveCell.Top myOLEObject.Height = ActiveCell.RowHeight myOLEObject.Width = ActiveCell.Width myOLEObject.Object.Caption = "" -- HTH RP (remove nothere from the email address if mailing direct) "James McDowell" wrote in message ... OK, I changed "wkCheck1" to "CHeckBox1" and it worked. Why will it not reconize the name that i have given it in the code. If I go into design mode and look at the properties, under the mane, it still shows CheckBox1 "Bob Phillips" wrote: Here is an example Dim StartLine As Long With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule StartLine = .CreateEventProc("Click", "wkCheck1") + 1 .InsertLines StartLine, _ "Dim ans" & vbCrLf & _ " ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _ " If ans = vbNo Then MsgBox ans" End With -- HTH RP (remove nothere from the email address if mailing direct) "James McDowell" <James wrote in message ... Hey, this is my first posting so bear with me... I have embedded several checkboxes on a spread sheet and i wish to write code for the change event, but i can not figure how to get to it. I created the checkboxes through code: MySheet.OLEObjects.Add ("Forms.CheckBox.1") Set myOLEObject = MySheet.OLEObjects.Item(xx) myOLEObject.Name = "wkCheck" & x myOLEObject.Activate myOLEObject.Left = ActiveCell.Left myOLEObject.Top = ActiveCell.Top myOLEObject.Height = ActiveCell.RowHeight myOLEObject.Width = ActiveCell.Width myOLEObject.Object.Caption = "" How can i write into the VBA the change event code? I have played with VBE before, but am not too strong in that area. Please help. I want to kick off a query when the check box is changed to true. Thank you, James McDowell |
| All times are GMT +1. The time now is 12:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com