Value change even does not work if Checkbox used

[Kamolga]

Hi,

I have checkboxes in cells (one per cell) that are related to those cell. They allow a user to select criteria but I wish he could select 6 maximum, so anytime one is used, I would like to count how many "True" I already have in that range (called 'TrueCriteria'). If I use the below macro, the event happens if I change the value manually from true to false but not if I do so using the checkbox

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 Then
Dim i As Long: i = Application.WorksheetFunction.CountIf([TrueCriteria], True)
MsgBox i
End If
End Sub

 

[Fluff]

Clicking a checkbox, does not trigger a change event, try putting the code into the check boxes

 

[mikerickson]

I'm curious, what do you want to happen when the seventh check box is checked.
Do you want it to BEEP and refuse to become checked?
Or do you want one of the previously checked boxes to become unchecked, if so, which box?

 

[Kamolga]

I'm curious, what do you want to happen when the seventh check box is checked.
Do you want it to BEEP and refuse to become checked?
Or do you want one of the previously checked boxes to become unchecked, if so, which box?

The idea is to make a list of all selected criteria selected and show it in the messagbox. If we have 6 already, I would just show the six previously selected and ask for user to unselect one before selecting the one he wanted (and put target.value to false).

 

[Kamolga]

Clicking a checkbox, does not trigger a change event, try putting the code into the check boxes

That was too simple Thanks!

 

[Fluff]

Glad we could help & thanks for the feedback.