asked on

Check that All Fields in a Userform are Completed before Submitting Data- excel vba

Good Morning Experts,

I have a need to ensure that all fields in a userform are filled prior to submitting the data to the workbook's data table and sending an automated email.

I've done some research and found what I thought was some cool code to loop through all the fields in the userform to check for completion... If they're filled then submit and email if not a message box will appear advising that all fields need to be completed.

Although I've attempted to adapt the code to my userform it's still not functioning properly... Seems to lose connection and the message box doesn't appear if a few fields are populated but the remaining are not.

My question is can someone assist me with figuring out what's causing the code not to work properly? I've even went into the sample workbook that the author of the code created to test it in its original environment but it was still not functioning properly. I just want to ensure its not user error on my end as I'm not the greatest with vba.

I appreciate any help with this and thank you for your time in advance!
Sample-Mail.xlsm
BCOMPLETE-vbaSample.xls

GrahamSkan

Your function will simply reflect the state of the last control tested in the loop. Also supressing error checking doesn't help.

This is your code

Public Function bComplete(frm As MSForms.UserForm) As Boolean
    ' check all controls
    Dim ctl    As Control
    ' if any error occurs, just exit
    On Error GoTo ExitFunc

    ' loop through controls, figure out type and
    For Each ctl In frm.Controls
        Select Case TypeName(ctl)
            Case "TextBox": If Len(ctl.Text) > 0 Then bComplete = True
            Case "CheckBox": If ctl.CheckBox <> "" Then bComplete = True
            Case "OptionButton": bComplete = ctl.Value
            Case "ComboBox", "ListBox": If ctl.ListIndex = -1 Then bComplete = False
            Case Else:    'bComplete = True
        End Select
    Next ctl

ExitFunc:
    Set ctl = Nothing
End Function

Open in new window

Here is revised version

Public Function bComplete(frm As MSForms.UserForm) As Boolean
    ' check all controls
    Dim ctl    As Control
    ' if any error occurs, just exit
    'On Error GoTo ExitFunc

    ' loop through controls, figure out type and
    For Each ctl In frm.Controls
        Select Case TypeName(ctl)
            Case "TextBox": If Len(ctl.Text) = 0 Then Exit Function
            Case "CheckBox": If ctl.Value = False Then Exit Function
            Case "OptionButton": If ctl.Value = False Then Exit Function
            Case "ComboBox", "ListBox": If ctl.ListIndex = -1 Then Exit Function
            Case Else:    'bComplete = True
        End Select
    Next ctl
bComplete = True
ExitFunc:
    'Set ctl = Nothing
End Function

Open in new window

Note that testing all option buttons and checkboxes wouldn't really be useful in a production version.

"Abys" Wallace

ASKER

Good morning @GrahamSkan... Thank you for working on this for me.. I pasted your revised code into my sample workbook... The message box appears advising that the form needs completing but it also still submits and triggers the email function.

I was looking to prevent submission and an email being sent if the form isn't completed. I there a way to do this? Also when I entered data in all the fields the message box was still popping up... I'm not sure what's causing this as well.. I apologize for my lack of knowledge as I am self taught and very novice with vba.

Respectfully
Sample-Mail.xlsm

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

@Graham: Why do you say "Note that testing all option buttons and checkboxes wouldn't really be useful in a production version"?

GrahamSkan

@Martin
You have pointed out the problem with option buttons. In the case of checkboxes, if they must all be set to True, there wouldn't be much point in having them.

Here is some code to test that there is exactly one option button set in each group

Public Function bComplete(frm As MSForms.UserForm) As Boolean
    ' check all controls
    Dim ctl    As Control
    Dim GroupNames() As String
    Dim CheckGroup() As Integer
    Dim i As Integer
    Dim v As Integer
    ' if any error occurs, just exit
    'On Error GoTo ExitFunc

    ' loop through controls, figure out type and
    For Each ctl In frm.Controls
        Select Case TypeName(ctl)
            Case "TextBox": If Len(ctl.Text) = 0 Then Exit Function
            Case "CheckBox": If ctl.Value = False Then Exit Function
            Case "OptionButton"
            For i = 0 To v - 1
                If ctl.GroupName = GroupNames(i) Then
                    Exit For
                End If
            Next i
            If i = v Then
                ReDim Preserve GroupNames(v)
                ReDim Preserve CheckGroup(v)
                GroupNames(v) = ctl.GroupName
                v = v + 1
            End If
            If ctl.Value = True Then
                CheckGroup(i) = CheckGroup(i) + 1
            End If
            Case "ComboBox", "ListBox": If ctl.ListIndex = -1 Then Exit Function
            Case Else:    'bComplete = True
        End Select
    Next ctl
    For i = 0 To v - 1
        If CheckGroup(i) <> 1 Then
            Exit Function
        End If
    Next i
bComplete = True
ExitFunc:
    'Set ctl = Nothing
End Function

Open in new window

GrahamSkan

This is the code in your production version:

Private Sub btnSendMail_Click()
'checks to see if all fields are filled prior to submitting or sending email
    If bComplete(Me) = False Then MsgBox "Form needs completing"
    'submit code
'Emails form data
RPV_Mail Me.comboMgr.Value, Me.comboSup, "BAN " & Me.txtBAN.Value & " Feedback", Me.textFeedback.Text
...

Open in new window

The mailing code is called regardless of the validity check, so you need to exit the Sub if the result is False

Private Sub btnSendMail_Click()
'checks to see if all fields are filled prior to submitting or sending email
    If bComplete(Me) = False Then 
         MsgBox "Form needs completing"
         EXit Sub
   Endif
    'submit code
'Emails form data
RPV_Mail Me.comboMgr.Value, Me.comboSup, "BAN " & Me.txtBAN.Value & " Feedback", Me.textFeedback.Text
...

Open in new window

Martin Liss

I have to admit I haven't tried your code but I don't see how that code can work since you don't set i or v to anything before you use them in line 17 so they're both zero. In any case VB only allows one option button in a set to be selected so there's no need to check for "exactly one".

Martin Liss

@abys757

I was looking to prevent submission and an email being sent if the form isn't completed.

All that is taken care of in my workbook.

"Abys" Wallace

ASKER

Thank you @martinLiss! Works perfectly! and I read through all the comments so I did learn something about validation and how to set "checkboxes" and "option buttons" in the future... Thank you again for the explanations and the modification~

GrahamSkan

@Martin
VB.Net would object to such uninitialised variables, but VBA doesn't mind.
You're right about the count never exceeding one. It is possible to have none or one, but no more. Code still works though.

Martin Liss

Here's a few observations on your code.

Private Sub OptMgr_Click()

frmRPVtracker.Width = 540

If OptMgr.Value Then comboList.Visible = True
If OptMgr.Value Then lblList.Visible = True

Call OptionChange(1)

End Sub

Open in new window

In that code you start off by setting the form's width to 540 which is narrower than it was before. That's unusual but if you always want it to be 540 you can set the forms Width property at design time or by putting frmRPVtracker.Width = 540 in the Userform_Activate event. Actually all you need is Width = 540 since the userform is assumed.

Secondly instead of

If OptMgr.Value Then comboList.Visible = True
If OptMgr.Value Then lblList.Visible = True

Open in new window

you can do

If OptMgr.Value Then 
    comboList.Visible = True
    lblList.Visible = True
End If

Open in new window

In any case you're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012

"Abys" Wallace

ASKER

Thank you Marty >>> Your help is greatly appreciated! :) the more vba discipline and recommended practices I can learn early the better. I did take you recommendations and modified my code accordingly.. At times I tend to "over code" (I know there's no such term but its the best way I can describe my poor coding habits, haha) to over-compensate for my lack of knowledge ...

In other words I tend to utilize the same line of code in this case the "resizing" option to ensure that the form is at 540 (the width expands) so that the comboList field can be seen in its entirety when the mgr, sup, or repII radial buttons are selected.... The initial width for the userform should've been set to 459 for cosmetic purposes more so...

Again thank you for the tips! I'll take all the knowledge I can...

Respectfully,
Tonya~

Martin Liss

Your code is fine for a beginner. I did notice one more thing. You should always put Option Explicit at the top of any code module. It will force you to explicitly define your variables. That may not seem like a good thing but it is because otherwise a spelling mistake may cause your code not to work as expected. For example you might have this code and be surprised that the MsgBox appears.

MySimpleVar = 5
If MySlmpleVar <> 5 Then
MsgBox "It's not 5"
End If

And this has nothing to do with Option Explicit but add this API definition under it

Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Open in new window

And add this Sub

Private Sub ChangeWidth(intWidth As Integer)
    
    If intWidth <= Width Then
        'Slowly shrink the width
        Do Until Width = intWidth
            Sleep 2
            Width = Width - 1
            DoEvents
        Loop

    Else
        'Slowly expand the width
        Do Until Width = intWidth
            Sleep 2
            Width = Width + 1
            DoEvents
        Loop
    End If
    
End Sub

Open in new window

And finally change to width-setting code lines from
frmRPVtracker.Width = 540
to
ChangeWidth 540

you might like the effect.
Don't forget to do the same for the frmRPVtracker.Width = 430 line.