Can Checkboxes trigger the Worksheet_Change code?

[Brad E.]

I have developed a spreadsheet with 78 checkboxes on it. I have linked each
checkbox to the cell underneath it. For ease of use for the end-user, I have
a Worksheet_Change code, and the keyboard user can simply enter 1/0, T/F,
True/False or a space to change the checkbox. The Worksheet_Change code
looks at the entry and adjusts the checkbox accordingly. Of course, these
checkboxes also have an effect on other parts of the spreadsheet. Everything
works fine for the keyboard user.

For the mouse-user, though, clicking the checkbox changes the linked-cell
entry, but does not trigger the Worksheet_Change event. Can I assign the
same macro to each checkbox, which will, in turn, edit the linked cell of the
box which was just checked? If so, my question is -- how do I determine the
checkbox number which was just checked, without writing code for each
checkbox? The cell edit (cell.value = cell.value) would then trigger the
Worksheet_Change code, which I know is working correctly.

 

[Dave Peterson]

If you used checkboxes from the Forms toolbar, then you can assign a common
macro to each of the checkboxes.
If you used checkboxes from the control toolbox toolbar, then you can't. You
could create a common procedure that each of the _click subroutines calls,
though.

But I think I would drop the linked cells completely.

This code goes into a General module.

The first routine populates A2:A79 with checkboxes from the Forms toolbar and
names the checkboxes based on its location.

The second routine is the macro that is assigned to each of the checkboxes.

Option Explicit
Sub RunOnce()

Dim CBX As CheckBox
Dim myRange As Range
Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim WhatCol As String

Set wks = ActiveSheet

FirstRow = 2
LastRow = 79
WhatCol = "A"

With wks
'remove any existing checkboxes
.CheckBoxes.Delete

For iRow = FirstRow To LastRow
With .Cells(iRow, WhatCol)
Set CBX = .Parent.CheckBoxes.Add _
(Top:=.Top, _
Left:=.Left, _
Height:=.Height, _
Width:=.Width)
.NumberFormat = ";;;"
End With
With CBX
.Name = "CBX_" & .TopLeftCell.Address(0, 0)
.Caption = ""
.OnAction = "'" & ThisWorkbook.Name & "'!CbxClick"
End With
Next iRow
End With
End Sub
Sub CBXClick()

Dim CBX As CheckBox

Set CBX = ActiveSheet.CheckBoxes(Application.Caller)

Application.EnableEvents = False
ActiveSheet.Range(Mid(CBX.Name, 5)).Value = CBool(CBX.Value = xlOn)
Application.EnableEvents = True

End Sub

========
This code goes behind the worksheet with the checkboxes:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRngToInspect As Range
Dim myIntersect As Range
Dim myCell As Range
Dim CBX As CheckBox

Set myRngToInspect = Me.Range("a2:A79")

Set myIntersect = Intersect(Target, myRngToInspect)

If myIntersect Is Nothing Then
Exit Sub
End If

For Each myCell In myIntersect.Cells
With myCell
Set CBX = Nothing
On Error Resume Next
Set CBX = Me.CheckBoxes("CBX_" & .Address(0, 0))
On Error GoTo 0

If CBX Is Nothing Then
MsgBox "Error in design with: " & .Address
Exit Sub 'get it fixed right away!
End If

Application.EnableEvents = False
Select Case LCase(.Value)
Case Is = " ", lcase("t"), lcase("true")
CBX.Value = xlOn
.Value = True
Case Else
CBX.Value = xlOff
.Value = False
End Select
Application.EnableEvents = True
End With
Next myCell

End Sub

 

[Jim Thomlinson]

You can call change event code. If you are calling the procedure from outside
of the sheet then you need to change the declaraion of the event code to
public...

Sub test()
Call Worksheet_Change(Range("A1"))
End Sub

Public Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Value
End Sub

 

[Brad E.]

Thanks, a lot, Dave. Everything works great.