Change event does not fire

[Lena]

Hello!
I have a worksheet with checkboxes. Each checkbox is linked to a cell. When
checkboxes are checked/unchecked the cell changes, but the Change event for
the sheet does NOT fire! Is that how it is supposed to be??? I wanted to use
this method instead of Checkbox Click event, because I have lots of
checkboxes. Also it seem to work before, but not anymore. Am I confusing
something?

Please help! Thanks a lot in advance.

P.S: Alternativly I can have another cell with formula linked to the
checkbox cell. This way Calculation event works. But it's not best practice

 

[Project Mangler]

Lena,

If I use this in excel 2003 the change event is triggered:

Option Explicit
Dim enableEvents As Boolean

Private Sub CheckBox1_Click()
If enableEvents = True Then Exit Sub
enableEvents = True
Range("A1") = 2
enableEvents = False
End Sub

You don't say exactly what a checkbox being "linked" to a cell means.
Posting your code would be best.

This is from Chip Pearson's website:

"For the Change event in particular, it should be noted that this is
triggered when a cell is changed by user action or by other VBA code, but is
not raised if the value of a cell is changed as a result of formula
calculation."

HTH

Hello!
I have a worksheet with checkboxes. Each checkbox is linked to a cell. When
checkboxes are checked/unchecked the cell changes, but the Change event for
the sheet does NOT fire! Is that how it is supposed to be??? I wanted to use
this method instead of Checkbox Click event, because I have lots of
checkboxes. Also it seem to work before, but not anymore. Am I confusing
something?

Please help! Thanks a lot in advance.

P.S: Alternativly I can have another cell with formula linked to the
checkbox cell. This way Calculation event works. But it's not best

practice

 

[Lena]

I don't have any code for checkbox itself. I use LinkedCell which is setup in
the propeties of the checkbox and changes the value from TRUE/FALSE whenever
the checkbox is checked/unchecked. I'm trying to catch an event when this
cell changes it's value.
I just use a test code for now for Worksheet_Change event and it doesn not
work when the cell is changed by the checkbox.

It does work however when I have another cell with the formula linking to my
LinkedCell of the checkbox. Then I can use Worksheet_Calculate event. But I
don't want to create another column just for that.

I have around 40 checkboxes and I don't want to use the Checkbox Click event
for each of them.

 

[Dave Peterson]

I'm not quite sure what you want to do, but instead of using 40 distinct _change
events, you could use a single _change event in a class module (for every
checkbox assigned to a specific group of checkboxes).

The "grouping" is done when the workbook opens.

This goes in a General module:

Option Explicit
Dim ChkBoxes() As New Class1
Sub Auto_Open()

Dim CBXCount As Long
Dim OLEObj As OLEObject

CBXCount = 0
For Each OLEObj In ThisWorkbook.Worksheets("sheet1").OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
CBXCount = CBXCount + 1
ReDim Preserve ChkBoxes(1 To CBXCount)
Set ChkBoxes(CBXCount).CBXGroup = OLEObj.Object
End If
Next OLEObj

End Sub

And then when you're in the VBE, do Insert|Class Module
The name of this class module is Class1 (it's important to match what's in the
code):

Then paste this in the newly opened code window:

Option Explicit
Public WithEvents CBXGroup As MSForms.CheckBox
Private Sub CBXGroup_Change()
With CBXGroup
MsgBox .Name & vbLf & .Value
End With
End Sub

You can read more info at John Walkenbach's site:
http://spreadsheetpage.com/index.php/file/multiple_userform_buttons_with_one_procedure