Dear group

How is it possible to assign a click event to a checkbox that has been placed within a worksheet using the following code..

Dim OleObjectsObject As OLEObject
Dim OleControl As OLEObjec
Dim CheckBox As MSForms.CheckBo

Set OleControl = OleObjectsObject.Add(ClassType:="Forms.CheckBox.1"
Set CheckBox = ExcelWorkSheet.OLEObjects(OleControl.Name).Objec

Best regards

Jim...

Hi Jim

Here is a example for a button
You can adapt it for a checkbox

Sub test()
Dim WS As Worksheet
Dim Btn As OLEObject
Set WS = ThisWorkbook.Worksheets("Sheet1")
With WS
Set Btn = .OLEObjects.Add(ClassType:="Forms.CommandButton.1" , _
Left:=.Range("C3").Left, Top:=.Range("C3").Top, _
Width:=100, Height:=30)
End With
Btn.Object.Caption = "Click Me"
Btn.Name = "TheButton"
With ThisWorkbook.VBProject.VBComponents(WS.CodeName).C odeModule
.InsertLines .CreateEventProc("Click", Btn.Name) + 1, _
"Msgbox ""Hi there"" "
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jim McLeod" wrote in message ...
Dear group,

How is it possible to assign a click event to a checkbox that has been placed within a worksheet using the following code...

Dim OleObjectsObject As OLEObjects
Dim OleControl As OLEObject
Dim CheckBox As MSForms.CheckBox

Set OleControl = OleObjectsObject.Add(ClassType:="Forms.CheckBox.1" )
Set CheckBox = ExcelWorkSheet.OLEObjects(OleControl.Name).Object

Best regards,

Jim...

An example of more complex code than Ron shows would be

With ThisWorkbook.VBProject.VBComponents(WS.CodeName).C odeModule
.InsertLines .CreateEventProc("Click", Btn.Name) + 1, _
vbTab & "If Range(""A1"").Value 0 Then " & vbCrLf & _
vbTab & vbTab & "Msgbox ""Hi""" & vbCrLf & _
vbTab & "End If"
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ron de Bruin" wrote in message
...
Hi Jim

Here is a example for a button
You can adapt it for a checkbox

Sub test()
Dim WS As Worksheet
Dim Btn As OLEObject
Set WS = ThisWorkbook.Worksheets("Sheet1")
With WS
Set Btn = .OLEObjects.Add(ClassType:="Forms.CommandButton.1" , _
Left:=.Range("C3").Left, Top:=.Range("C3").Top, _
Width:=100, Height:=30)
End With
Btn.Object.Caption = "Click Me"
Btn.Name = "TheButton"
With ThisWorkbook.VBProject.VBComponents(WS.CodeName).C odeModule
.InsertLines .CreateEventProc("Click", Btn.Name) + 1, _
"Msgbox ""Hi there"" "
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jim McLeod" wrote in message
...
Dear group,

How is it possible to assign a click event to a checkbox that has been
placed within a worksheet using the following code...

Dim OleObjectsObject As OLEObjects
Dim OleControl As OLEObject
Dim CheckBox As MSForms.CheckBox

Set OleControl = OleObjectsObject.Add(ClassType:="Forms.CheckBox.1" )
Set CheckBox = ExcelWorkSheet.OLEObjects(OleControl.Name).Object

Best regards,

Jim...

Hi Jim

http://www.j-walk.com/ss/excel/tips/tip96.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jim McLeod" wrote in message ...
Hi Ron and Bob,

Within the following line of code...
With ThisWorkbook.VBProject.VBComponents(ExcelWorkSheet .CodeName).CodeModule

I am obtaining the following error...
Run-time error '1004'
Programmic access to Visual Basic Project is not trusted

Any ideas?

Regards,

Jim...

Thanks Ron, one to add to QDE!

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ron de Bruin" wrote in message
...
Hi Jim

http://www.j-walk.com/ss/excel/tips/tip96.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jim McLeod" wrote in message
...
Hi Ron and Bob,

Within the following line of code...
With
ThisWorkbook.VBProject.VBComponents(ExcelWorkSheet .CodeName).CodeModule

I am obtaining the following error...
Run-time error '1004'
Programmic access to Visual Basic Project is not trusted

Any ideas?

Regards,

Jim...

Hi Bob

I add it aslo the the last versions of the SendMail Add-ins Bob.
(for the delete VBA code option)

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bob Phillips" wrote in message ...
Thanks Ron, one to add to QDE!

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ron de Bruin" wrote in message
...
Hi Jim

http://www.j-walk.com/ss/excel/tips/tip96.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jim McLeod" wrote in message
...
Hi Ron and Bob,

Within the following line of code...
With
ThisWorkbook.VBProject.VBComponents(ExcelWorkSheet .CodeName).CodeModule

I am obtaining the following error...
Run-time error '1004'
Programmic access to Visual Basic Project is not trusted

Any ideas?

Regards,

Jim...