CODEWITHSUNDEEP
vbaexcel
herman925
herman925

Reputation: 91

(Excel Userform) Check if all checkboxes in a Userform are checked

Never tried UserForm checkboxes before so I don't even know how to point to the Checkboxes in a Userform.

This is what I have at the moment....and I know, I know, it is completely wrong. Please help?

Private Sub Step1_Confirm_Click()
    Dim i As Byte
    Dim Done As Boolean

    For i = 1 To 4
        If Step1_(i).value = True Then
            Done = True
        End If
    Next i

    If Not Done = True Then
        MsgBox "Please make sure you have done all"
    End If

End Sub

Basically I have:

  1. A Userform called IOSP_Acc_R_Approval_Step1

  2. 4 checkboxes called Step1_1; Step1_2; Step1_3; Step1_4

  3. A button called Step1_Confirm

  4. I want the button to show Error, if not all checkboxes are checked - meaning that all checkboxes have to be checked....(in case my English is too bad to convey my meaning)

Upvotes: 3

Views: 5522

Answers (3)

user6637260
user6637260

Reputation: 

    Done=true 
For i = 1 To 4
            Done = Done*Step1_(i).value
        Next i
if done `then`
msgbox "All checkboxes are checked"
end if

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33672

Try the code below (explanations inside the code as comments):

Private Sub Step1_Confirm_Click()

Dim i As Long
Dim Ctrl As Control

' loop through all user_form control
For Each Ctrl In IOSP_Acc_R_Approval.Controls
    If TypeName(Ctrl) = "CheckBox" Then ' check if control type is Check-Box
        If Ctrl.Value = True Then ' check if check-box is checked
            i = i + 1
        End If
    End If
Next Ctrl

If i < 4 Then ' not all 4 check-boxes are checked
    MsgBox "Please make sure you have done all"
End If

End Sub

Upvotes: 4

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

You can do this by:

  • assume that all checkboxes are checked by setting a flag to True
  • iterate the checkboxes and if one is not checked set the flag to False and exit
  • at the end of the loop, if all checkboxes were checked then the flag is still True

You can refer to the checkboxes dynamically by using the Me.Controls collection and pass in the name of the checkbox like "Step1_" & i.

Example code:

Option Explicit

Private Sub Step1_Confirm_Click()

    Dim i As Long '<-- use Long, not Byte
    Dim blnResult As Boolean

    ' set a flag to assume that it is true that all checkboxes are checked
    blnResult = True

    ' get the value of each check box
    For i = 1 To 4
        If Me.Controls("Step1_" & i).Value = False Then
            blnResult = False
            Exit For '<-- skip loop if at least one box not checked
        End If
    Next i

    ' check the value of the flag
    If blnResult = False Then
        MsgBox "Please make sure you have done all"
    Else
        ' all boxes checked ...
        MsgBox "All checked"
    End If

End Sub

Upvotes: 2

Related Questions