Userform: Stop Script if Required Field Blank

[Natalie50208]

I'm working on a userform (my first) and have managed to stump myself on the following:

I have several fields that are 'required'. If a required field is not completed, a message box is prompted when the user clicks a button to create an excel form. I think I have that piece...so far so good....

My issue is that if the message box is prompted, I want to exit the script (the remaining code loads the excel sheet and clears the user form [I don't want the user to have to start from scratch if they forget a field]).

Here's the code that I have to prompt the message box:

Select Case True
Case Revenue_Entry.txtDate.Value = ""
MsgBox "Please Complete All General Information"
Cancel = True
Case Revenue_Entry.txtRqstr.Value = ""
MsgBox "Please Complete All General Information"
Cancel = True
Case Revenue_Entry.txtProCtr.Value = ""
MsgBox "Please Complete All General Information"
Cancel = True
Case Revenue_Entry.txtProCtrNm.Value = ""
MsgBox "Please Complete All General Information"
Cancel = True
Case Revenue_Entry.cboME.Value = ""
MsgBox "Please Complete All General Information"
Cancel = True
Case Revenue_Entry.cboPostClose.Value = ""
MsgBox "Please Complete All General Information"
Cancel = True
Case Revenue_Entry.cboEntry.Value = ""
MsgBox "Please Complete All General Information"
Cancel = True
Case Revenue_Entry.cboAutoRev.Value = ""
MsgBox "Please Complete All General Information"
Cancel = True
End Select

I've tried inserting the following throughout, but it didn't stop the script when the message boxes were prompted:

If MsgBox("Please Complete All General Information") = True Then
Exit Sub
End If


Any advice will be greatly appreciated

 

[JSSguitars]

Do you have a frame around the "General Information" section, or some other characteristic of the required fields that sets them apart, like a different BackColor? That would help in creating a more simple answer to your request. Let me know...

 

[mikerickson]

Perhaps

Dim myRRay As Variant, oneControl As Variant

With Revenu_Entry
    myRRay = Array(.txtDate, .txtRqstr, .txtProCtr, .txtProCtrNm, .cboME, .cboPostClose, .cboEntry, .cboAutoRev)
End With

Cancel = False
For Each oneControl In myRRay
    allAreFull = Cancel Or (oneControl.Value = vbNullString)
Next oneControl

If Cancel Then
    MsgBox "Please Complete All General Information"
    Exit Sub
End If

Rem other code

 

[Natalie50208]

Do you have a frame around the "General Information" section, or some other characteristic of the required fields that sets them apart, like a different BackColor? That would help in creating a more simple answer to your request. Let me know...

The fields are not in a frame; rather spaced through out the form.

 

[Norie]

Natalie

Where is this code located?

What do you expect Cancel=True to do?

By the way it's not a good idea using Select Case like that.

I know it's possible but it can actually cause more problems than it solves.

Have you looked at the individual controls events?

For example Exit?

 

[JSSguitars]

I would shade each of the required fields a specific color. At the top of UserForm I'd put a label with something like, "All shaded fields must be completed."

Make sure that the BackColor (shaded red below) is the same as whatever you change the backcolor to for your required fields. You can select all of the required TextBoxes at once and change the BackColor for all of them at the same time. This assumes that the required fields are textboxes. If not, we can amend this.

[COLOR=blue]Dim[/COLOR] oFields [COLOR=blue]As[/COLOR] Control
[COLOR=blue]For Each[/COLOR] oFields [COLOR=blue]In[/COLOR] UserForm1.Controls
    [COLOR=blue]If[/COLOR] TypeName(oFields) = "TextBox" [COLOR=blue]Then[/COLOR]
        [COLOR=blue]If[/COLOR] oFields.BackColor = [COLOR=red]&HC0E0FF[/COLOR] [COLOR=blue]And[/COLOR] oFields.Value = "" [COLOR=blue]Then[/COLOR]
                MsgBox "You need to fill in the shaded fields!", vbOKOnly
                [COLOR=blue]Exit Sub[/COLOR]
        [COLOR=blue]End If[/COLOR]
[COLOR=blue]   End If[/COLOR]
[COLOR=blue]Next[/COLOR] oFields