return user to UserForm if not all fields have been populated

craigexcel

Active Member
Joined
Jun 28, 2006
Messages
295
Office Version
  1. 2016
Platform
  1. Windows
I have a UserForm with several fields on it: 5 are 'required' and the other 6 are 'optional'. If a user doesn't enter a value for ALL of the 5 'required' fields, how do I get the code to return the user to the UserForm (with, I assume, the values they already entered, still showing)?

I have the AutoOpen code that starts things off, and then, in turn, loads the UserForm with the fields that need to be populated.

I can't figure out how to route the user back to the UserForm, to enter the 'missed' data.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Hello Craig

Do you mean that the user can close the userform even when the mandatory fields are complete? Do you use a button to close the form?

You could code the close button for instance, something like:

Code:
Private Sub CommandButton1_Click()
    With Me
        If Len(.TextBox1.Value) * Len(.TextBox2.Value) * Len(.TextBox3.Value) * _
                Len(.TextBox4.Value) * Len(.TextBox5.Value) = 0 Then
            MsgBox "please complete all fields"
        Else
            Unload Me
        End If
    End With
End Sub
 
Upvote 0
Additionally if you want to kick off the same validation when the user attempts to close the form with the red cross at the top right;

Code:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
        Cancel = True
        Call CommandButton1_Click
    End If
End Sub
 
Upvote 0
Jon,
Thanks for your replies. On the face of it, that would seem to work well. However, I pasted your suggested code as written, but received an error ("Compile error: Method or data member not found"). I tried removing the leading "." in front of the textbox names, and also tried inserting the userform's name in front (i.e. userform.txtbox...), both without success. What's the syntax for the name for the textboxes and the listbox that I have?
 
Upvote 0
Hi Craig

Sorry to answer with another question but...

What is the name of the userform?
What is the name of the command button to close the userform?
What are the names of the 5 mandatory controls?
 
Upvote 0
Hi Craig

Sorry to answer with another question but...

What is the name of the userform?
What is the name of the command button to close the userform?
What are the names of the 5 mandatory controls?

Q1: form_TERdata
Q2: CommandButton2 (Cancel button)
Q3:
1. txtbox_EmplName
2. lbox_Div
3. txtbox_TravelDate
4. txtbox_TravDate1
5. txtbox_TravPurpose1


Oops. To answer your questions from earlier in the thread, yes, the OK button is intended to do the checking of the 'required' fields, and then, once all have been populated, the UserForm is closed and the respective values are copied to the appropriate cells in the worksheet.
 
Last edited:
Upvote 0
How about;

Code:
Private Sub CommandButton2_Click()
    With Me
        If (.lbox_Div.ListIndex + 1) * Len(.txtbox_EmplName.Value) * Len(.txtbox_TravDate1) * _
                Len(.txtbox_TravelDate.Value) * Len(.txtbox_TravPurpose1.Value) = 0 Then
            MsgBox "please complete all fields"
        Else
            Unload Me
        End If
    End With
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
        Cancel = True
        Call CommandButton2_Click
    End If
End Sub
 
Upvote 0
I copied the first part of your suggestion, just to be sure that worked for me. It works as far as verifying that the 'required' fields have all been populated. Thanks.

However, it doesn't route me back to being able to continue to enter data in the UserForm. The code displays your 'error' message, and then exits. I need for the code to return to the UserForm so the user can enter all required data.
 
Upvote 0
How is the userform being closed? Because this should be the close code and you shouldn't have another way of exiting the form. In thise code the only way to exit the form is to complete all fields. Have you perhaps got other code that nay be interfering?
 
Upvote 0
Just noticed, "code to close the userform" is in CommandButton1 (i.e. the OK button), not CommandButton2 (the Cancel button). As mentioned before, once the OK button is clicked, the code needs to verify entry of all required fields, copy the values to the appropriate cells in the worksheet, and close the userform, leaving the workbook open for further data entry.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top