Adding Embedded OLE Objects on a Spread Sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Here is an example

Dim StartLine As Long

With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
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)
 
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
 
Is this pre or post my response?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
It tells me that my event handler is invalid.

Bob Phillips said:
Here is an example

Dim StartLine As Long

With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
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)
Click to expand...
 
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???
 
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
 
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)
 
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)
 
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!!!
 
James,

Can you send me your workbook so I can take a look?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top