Assigning click event to OleObjects checkbox

[Guest]

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

 

[Ron de Bruin]

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).CodeModule
.InsertLines .CreateEventProc("Click", Btn.Name) + 1, _
"Msgbox ""Hi there"" "
End With
End Sub

 

[Bob Phillips]

An example of more complex code than Ron shows would be

With ThisWorkbook.VBProject.VBComponents(WS.CodeName).CodeModule
.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]

Hi Jim

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

 

[Bob Phillips]

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]

Hi Bob

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