Control if all checkboxes are FALSE (then...)

Hi, I'm new to this forum and a newbie to coding with vba. I've searched the net for a solution for my current problem, but haven't found any suitable solution yet. Maybe some of you here can help me. Ok, to the topic: I have a set of checkboxes (form control) and I'd like to write a vba code to control if all checkboxes are FALSE (or value = 0). If so, I simply want a MsgBox to appear and then Exit Sub. I've written a code that goes loops through all checkboxes, but it doesn't give the desired end result if all checkboxes are FALSE. Here's the code I have so far:

For Each cb In ActiveSheet.Shapes
If cb.Type = msoFormControl Then
If cb.FormControlType = xlCheckBox Then
If cb.ControlFormat.Value = 0 Then
iX = iX + 1
If iX = 0 Then
MsgBox "No checkboxes are selected"
Range("B3").Select
Exit Sub
End If
End If
End If
End If
Next cb

Any ideas?

Cheers,
Magne E.

Hi, you could try to write your third If like this:"if cb.value = false then" (checkbox not checked). Also you add 1 to your iX variable if checkbox is unchecked, what is initial value of it?

Originally Posted by The_Greg

Hi, you could try to write your third If like this:"if cb.value = false then" (checkbox not checked). Also you add 1 to your iX variable if checkbox is unchecked, what is initial value of it?

Hi, and thanks for your quick reply. The initial value of iX is 0. But your suggestion of putting "if cb.value = false..." doesn't seem to work. What happens is that the code loops through all the checkboxes and then simply contiues with the rest of the code (i.e. trying to copy the rows to which the checkboxes are linked). Any ideas?

Sorry, doesn't seem to work. My code loops through the checkboxes, but doesn't stop if none of them are checked. It simply continues with the rest of the code (i.e. trying to copy the rows to which the checkboxes are linked). Any ideas?

Hi again, I found a solution! Here's the new code:

Dim cb As Shape
Dim valid As Boolean

valid = False
For Each cb In ActiveSheet.Shapes
If cb.Type = msoFormControl Then
If cb.FormControlType = xlCheckBox Then
If cb.ControlFormat.Value = xlOn Then
valid = True
Exit For
End If
End If
End If
Next cb

If valid = False Then
MsgBox "No checkbox selected"
Range("B3").Select
Exit Sub
End If