How do I get an OLEObjects check box to call a function when click
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
|