Data Validation


New Member
May 13, 2015
How can I create a code to prompt the user when the date isn't entered as mm/dd/yyyy format? Also, how can I ensure that the user selects either the Prospect button or the Current button? Thank you. Here is my code:

Private Sub Label1_Click()
btnProspect.GroupName = "CustomerType"
btnCurrent.GroupName = "CustomerType"
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdEnter_Click()
ActiveSheet.Unprotect Password:="fscbcall"
Dim RowCount As Long
Dim benefits, total As Single

' Write data to worksheet
RowCount = Worksheets("Current Month").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Current Month").Range("A1")
.Offset(RowCount, 0).Value = Me.txtInitials.Value
.Offset(RowCount, 1).Value = Me.txtName.Value
.Offset(RowCount, 2).Value = Me.btnProspect.Value
.Offset(RowCount, 2).Value = Me.btnCurrent.Value
.Offset(RowCount, 3).Value = Me.txtDate.Value
.Offset(RowCount, 4).Value = Me.chkTop.Value
.Offset(RowCount, 5).Value = Me.txtRemarks.Value
.Offset(RowCount, 6).Value = Me.txtFollowupdt.Value
.Offset(RowCount, 7).Value = Me.txtFollowupcmt.Value
If Me.chkTop.Value = True Then
.Offset(RowCount, 4).Value = "Yes"
.Offset(RowCount, 4).Value = "No"
End If
If btnCurrent.Value = True Then
.Offset(RowCount, 2) = "Current"
ElseIf btnProspect.Value = True Then
.Offset(RowCount, 2) = "Prospect"
End If

End With
'Clear the form

Unload Me

With Worksheets("Current Month")
With .Range("A7:H200")
.Sort Key1:=.Columns("A"), Order1:=xlAscending, Key2:=.Columns("D"), Order2:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
End With

ActiveSheet.Protect Password:="fscbcall", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True

End With
End Sub
Private Sub Textbox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not txtDate.Value Like "##[/]##[/]####" Then
MsgBox "Must Be Format mm/dd/yyyy"
Cancel = True
Textbox1.Value = ""

Else: chkTop.Value

End If
End Sub

Private Sub txtRemarks_Change()

End Sub

Private Sub UserForm_Click()

End Sub

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Why don't you just use a regular cell in excel and data validation? Set the dates and error messages based on the normal rules and then just set the format equal to mm/dd/yyyy in the format so no matter what date is entered, it's in the format you need. Does it have to be a text box?

Also, what is this being used for that would require the user to push either of the buttons? You can assign some hidden cells to be true or false and assign the macro on click to change those cell attributes and then only proceed to the rest of your code if one of those cell values is true.
Upvote 0
thank you for your response. the userform is for entering customer call data which is then plugged into a spreadsheet. The buttons are for the sales person to select whether the call was made to a current customer or a prospect customer, i'm using those specifics to track the numbers of each category called so if they do not click a button the call isn't recorded in the totals. Also everyone seems to put a date in different and I want them to be alerted in the user form when they press the "ok" button that if they entered the date in the wrong format that they need to change it.
Upvote 0

Forum statistics

Latest member
Mark Prillman

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
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 "".
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