How do I get an OLEObjects check box to call a function when click

[Guest]

I have the following code:

Sub addCheckBox(numrows As Integer)
....
' c is defined as a range in the active sheet in:
' Range("B11:B" & (10 + numrows))
....
For Each c In ws.Range("B11:B" & (10 + numrows))
counter = counter + 1
' This sets a cell which will define the location of the check box
Set cellUnder = c.Offset(0, -1)
' The next line adds the control and sizes and positions
' the control over a cell in the DailyTasks named range.
Set cb = ws.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
Left:=width, _
Top:=cellUnder.Top, _
width:=width, _
Height:=cellUnder.Height)

cb.name = (counter + 10)
cb.LinkedCell = "A" & (counter + 10)
cb.PrintObject = False
...
Next c
Application.ScreenUpdating = True
End Sub

As you can see, each check box was created in a loop and linked to the cell
behind it.
I have another function that I would ideally like to run each time the check
box is clicked:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If (Target.Column = 1) Then
If (Range("A" & (Target.row)).Value = True) Then
RunNow (Target.row)
End If
End If
End Sub

However, when I add the OLEObjects check boxes, when I click it, the cell is
changed to True to reflect my action, but Worksheet_Change never is called.
Only by manually typing in "True" in the cell will Worksheet_Change (and aso
RunNow) run.

How can I either:
(a) use my exisiting code and get my check boxes to call the
Worksheet_Change function or
(b) create a macro that every check box will link to which will call the
function on the specific row in which the check box is embedded?

Thanks!
Dan

 

[$600b decifit by Jan 2005, then $800 with Iran+N.K]

In your onclick("")
You should put the name of your function there between ""

 

[Dave Peterson]

If I want multiple checkboxes that do essentially the same thing, I'll use
checkboxes from the forms toolbar.

If you want to try this:

Option Explicit

Sub testme()
addCheckBox 10
End Sub

Sub addCheckBox(numrows As Integer)

Dim myCell As Range
Dim CBX As CheckBox
Dim CellUnder As Range
Dim counter As Long
Dim ws As Worksheet
Set ws = ActiveSheet

counter = 0
For Each myCell In ws.Range("B11:B" & 10 + numrows).Cells
counter = counter + 1

Set CellUnder = myCell.Offset(0, -1)
Set CBX = ws.CheckBoxes.Add( _
Left:=CellUnder.Left, _
Top:=CellUnder.Top, _
Width:=CellUnder.Width, _
Height:=CellUnder.Height)

With CBX
.Name = "CBX_" & counter + 10
.LinkedCell = CellUnder.Address(external:=True)
.PrintObject = False
.OnAction = ThisWorkbook.Name & "!cbxclick"
End With
CellUnder.NumberFormat = ";;;"
Next myCell
Application.ScreenUpdating = True
End Sub

Sub CBXClick()
Dim CBX As CheckBox

Set CBX = ActiveSheet.CheckBoxes(Application.Caller)

If CBX.Value = xlOn Then
MsgBox CBX.TopLeftCell.Row
Else
MsgBox CBX.TopLeftCell.Column
End If

End Sub