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