Validating Required Fields in Userform

R

robinsn

New Member
Joined
Jul 14, 2010
Messages
13
Hello

I have created a userform with around 10 fields. 6 of these fields are required fields. There is a command button on the userform called "Add to List". When this command button is clicked, all the data on the userform is transferred to an spreadsheet in the background. The required fields are listed below in the order as they appear on the userform
  • Date
  • Docket Number
  • Customer
  • Amount
  • Driver
  • Truck Rego Number
When I place the cursor on the date field and tab through all the fields, filling in the appropriate data, I then click on the button "Add to List". Everything is transferred to the spreadhsheet.

The issue arise as follows:
I place the cursor with the mouse on the Customer field and fill in the customer and tab through the remaining fields filling in the appropriate data. I then click on the "Add to List" button and it transfers the data to the spreadsheet. The userform is cleared out allowing me to enter the next record. However the fields for Date and Docket Number are blank.

I would like to put in some validation code when the "Add to List" button is clicked, that would come up with a message saying that in this example "Date is blank, please enter a valid date"

I have tried various options but no success. I would also like to avoid getting a "Run Time Error 13" message. I want to end user to only see the user form.

Any help would be appreciated.

Thanks
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
You can check that TextBoxes aren't empty like this:

Code: 
If Len(TextBox1.Text) = 0 Then
    MsgBox "Please complete"
    Exit Sub
ElseIf Len(TextBox2.Text) = 0 Then
    MsgBox "Please complete"
    Exit Sub
'More ElseIfs
End If
 
Upvote 0
Hello Andrew

Thanks for the response, I tried this code but it still allows me to add the data. In the same "Sub" I have the following If Statement.

Private Sub txtStartDate_Exit(ByVal Cancel As MSForms.ReturnBoolean) 'Checks Start Date is in correct date format
If IsDate(txtStartDate.Value) Then
txtStartDate.Value = Format(DateValue(txtStartDate.Value), "dd/mm/yy")
Else
MsgBox "Please enter a valid date format."
Cancel = True
End If


I then added a second If statement in the same "Sub" to ensure the date field would not be blank.

Is this where I am going wrong? Do I need to input your If Statement in another section of the page as a separte sub?


Niall
 
Upvote 0
Hi Andrew

Thanks for the response. I added in the code into the Click Event procedure for the "Add to List" button. However when I tried to enter data without the date, a message comes up saying "Run Time Error 13: Type mismatch". I then click on the debug button and it brings me directly to the line called ActiveCell.Value = DateValue(txtDocketDate.Text)

The full coding for the event procedure is shown below.

Is there something I need to add or change in the coding below?

Private Sub cmdAddToList_Click()

'Copies data from the form into the worksheet'
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
'enter docket date with date format required'
ActiveCell.Value = txtDocketDate.Text
ActiveCell.Value = DateValue(txtDocketDate.Text)
Selection.NumberFormat = "dd/mm/yy"
ActiveCell.Offset(0, 1).Select

'enter Client name'
ActiveCell.Value = cboClient.List(cboClient.ListIndex)
ActiveCell.Offset(0, 1).Select
<o:p> </o:p>
'enter Truck Number and Driver'
ActiveCell.Value = cboTruckNo.List(cboTruckNo.ListIndex)
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = cboDriver.List(cboDriver.ListIndex)
ActiveCell.Offset(0, 1).Select
<o:p> </o:p>
'Ensure that docket date is not blank'
If Len(txtDocketDate.Text) = 0 Then
MsgBox "Docket Date is blank. Please complete."
Exit Sub
End If
<o:p> </o:p>
' offer to clear the form when done'
If MsgBox("Clear the form?", _
vbYesNo + vbQuestion, "Private Works") = vbYes Then
cboClient.Value = ""
cboTruckNo.Value = ""
cboDriver.Value = ""
UserForm_Initialize
Me.Repaint
End If

End Sub
 
Upvote 0
You need to put the code I posted at the beginning of that procedure. Note that what I posted was just an example. You will need to change the names of the TextBoxes and add more checks.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Refer to a sheet selected by combobox
Replies
2
Views
134
dmt32
D
P
  • Solved
Transferring data between two userforms problem .
Replies
7
Views
350
Pacman52
P
D
  • Question
VBA Loop Through Named Ranges
Replies
1
Views
210
Jeffrey Mahoney
Jeffrey Mahoney
I
  • Question
Code runs out of line & edit advice needed please
Replies
2
Views
150
Micron
M
P
  • Question
Command Buttons on Userform
Replies
5
Views
225
PaulWJ
P

Forum statistics

Threads
1,216,119
Messages
6,128,946
Members
449,480
Latest member
yesitisasport

Share this page

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