Verify CheckBoxes' value then do an action

Davester · Mar 19, 2015

Hi there,
I have a form (not a userform but an excel spreadsheet for work) which contains 13 checkboxes. I need ALL checkboxes to be checked before the button sends me the e-mail. I have the working code for the e-mail but I can't seem to get the code right for the checkboxes.

Basically, I need code to do this :

1) Check if ALL checkboxes are checked.
2) If not, MsgBox telling the user to check all of them and stop the Loop.
3) If all boxes are checked, do this action.

Right now I got :

Sub BoxCheck()
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox And obj.Object.Value = True Then
Mail_ActiveSheet
Else
MsgBox ("Please make sure to check all boxes before sending the file")
End If
Next
End Sub

But I can't seem to get a loop going and right now it sends me an email for each checkbox that is checked and opens a msgbox for each box that isn't checked.

Any ideas ?

NoS · Mar 19, 2015

Perhaps something like this ?

Code:

Sub BoxCheck()
    
For Each obj In ActiveSheet.OLEObjects
    If TypeOf obj.Object Is MSForms.CheckBox And obj.Object.Value <> True Then
        MsgBox obj.Name & "  is not checked."
        Exit Sub
    End If
Next

MsgBox "If you see this message, all check boxes are checked"

'Mail_ActiveSheet

End Sub

Davester · Mar 20, 2015

NoS said:

Perhaps something like this ?

Code:

Sub BoxCheck()
    
For Each obj In ActiveSheet.OLEObjects
    If TypeOf obj.Object Is MSForms.CheckBox And obj.Object.Value <> True Then
        MsgBox obj.Name & "  is not checked."
        Exit Sub
    End If
Next

Mail_ActiveSheet

End Sub

Works perfectly. Changed the last part to do what I want.

Thanks a bunch!

Verify CheckBoxes' value then do an action

Davester

New member

NoS

Member

Davester

New member