VBA Userform check for all fields completed

[AMoss79]

Hi,

Can anyone suggest a loop I can use to check all fields have been completed including radio buttons for a form prior to submitting.

Thanks

 

[Tinbendr]

something like this.

Dim Ctrl As Control

For Each Ctrl In Me.Controls
    Select Case TypeName(Ctrl)
    
        Case "OptionButton"
            If Not Ctrl Then
                MsgBox Ctrl.Name & " Must be set"
                Ctrl.SetFocus
                Exit For
            End If
        Case "CombBox"
        
        Case "TextBox"
      
    End Select
Next

 

[AMoss79]

Thanks,

I'm new to coding in VBA

Some of the buttons are yes/ no option buttons so need to be considered as a group/ pair as we only require one to be completed.
Can this be accounted for also?

 

[Tinbendr]

Is each group within a frame?

 

[mikerickson]

You should count how many controls need to have a value. Textboxs, combo,..... When counting groups of option buttons, that counts as only one.

Then put code like this

Dim CountOfFilledControls as Long
Dim oneControl as msForms.Control

For Each OneControl in Userform1.Controls
    Select Case TypeName(OneControl)

        Case "OptionButton"
            If oneControl.Value Then CountOfFilledControls = CountOfFilledControls + 1
        Case "TextBox", "ComboBox", "RefEdit", "ListBox"
            If oneControl.Text <> vbnullString Then CountOfFilledControls = CountOfFilledControls + 1

    End Select
Next oneControl

If CountOfFilledControls = [your number] Then
    MsgBox "all good"
Else
    MsgBox "something missing"
End IF

 

[Tinbendr]

If they are only Yes/No and the form can't skip any option, go ahead and set the options to all No at startup. This way you have already set at least one choice prior to user intervention.

If you had four option buttons, with yes/no choices and we want to set them to all No's to start with;

Private Sub UserForm_Initialize()
Me.OptionButton2 = True
Me.OptionButton4 = True
End Sub

 

[AMoss79]

Hi, Some are and some aren't, if using frames make it easier/ more robust then it can be done.

 

[Tinbendr]

What I do is to put each group in it's own frame. Then in each optionbutton click event, I write that state to the Frame's Tag property.

Private Sub OptionButton1_Click
   Me.Frame1.Tag = "True"
End Sub

Then, I test each Tag property to determine Selection.

If Me.Frame1.Tag = "True" then
    Sheet1.Range("A5") = "X"' Or what ever you need to happen.
End if

It's a lot of work up front, but it's easier to deal with that all the options at the end.

 

[mikerickson]

What I do is to put each group in it's own frame. Then in each optionbutton click event, I write that state to the Frame's Tag property.

I like it. Particularly this possibility

Private Sub OptionButton1_Click
   Me.Frame1.Tag = "1"
End Sub

Private Sub OptionButton2_Click
   Me.Frame1.Tag = "2"
End Sub

 

[Richard U]

Frames make life easier. but if you don't have a frame and need to check if one of two buttons are selected, you can use XOR logic.

if opt1.value XOR opt2.value then (more code)

It will return true if EXACLY ONE button in the pairing has been selected, but as others have said, frames are still cleaner. even with XOR logic.

Then you can do validation like this.
function MyValidator() as boolean
dim flag as boolean
Dim CTL as control
flag = false
For each CTL in MyFrame.controls
if ctl.value = true then flag = true
Next CTL
.
,
,
More code

MyValidator = flag
end function