Results 1 to 3 of 3

Thread: Verify CheckBoxes' value then do an action

  1. #1

    Verify CheckBoxes' value then do an action



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 ?

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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

  3. #3
    Quote Originally Posted by NoS View Post
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •