vba to check if any of checkboxes are ticked

[tonywatsonhelp]

Hi Everyone,

I have a set of checkboxes there names are CheckBox21 to 30

I just need a macro that if none of the boxes are checked I get a messagebox saying "please select one"

how can I do this?

Thanks

Tony

 

[steve the fish]

Here would be a possibility

flag = False
For Each ctrl In Me.Controls
    If TypeName(ctrl) = "CheckBox" Then
        If ctrl.Value = True Then
            flag = True
            Exit For
        End If
    End If
Next
If flag = False Then
    MsgBox "Please select checkbox"
    Exit Sub
End If

Apply it to a command button etc to trigger

 

[tonywatsonhelp]

Hi Steve the fish and everyone,
Your idea did not work i'm sorry to say, I think that code is for checkboxes on a form my are just in the tab
I also have more checkboxes then these so I need it to only ref checkboxes 21 to 30 please help if you can

Thanks

Tony

 

[tonywatsonhelp]

Hi Everybody,

just trying to be helpful this code works but as you can see doing it for just two checkboxes is long winded 10 would be creazy
please help simplify this for me.

[FONT=Calibri][SIZE=3][COLOR=#000000]Private Sub cbrAll_Click1232()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Set ws = ActiveSheet[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If ws.OLEObjects("Checkbox21").Object.Value =False & ws.OLEObjects("Checkbox22").Object.Value = False Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]MsgBox ("Hello")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Else[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]MsgBox ("bye")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]     [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]