Data Validation

[paulgfscb]

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"
Else
.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, _
DataOption1:=xlSortNormal
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"
Textbox1.SetFocus
Cancel = True
Textbox1.Value = ""

Else: chkTop.Value

End If
End Sub

Private Sub txtRemarks_Change()

End Sub

Private Sub UserForm_Click()

End Sub

 

[paulgfscb]

*bump

 

[kasanari]

Hello,

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.

 

[paulgfscb]

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