Userform textbox date input problem

[GraingerT]

Hi

I'm hoping someone can help with this problem. I have a workbook that uses a userform to populate various cells. One of the cells uses a textbox to populate a date (I initially used a monthview datepicker to select this but an update has removed the options so I've changed it to a textbox. The code I've used confirms that the input is a date, then populates the relevant cell using CDate, but if someone enters an impossible date, such as 32 Dec 2019, it populates the cell as 19 Dec 2032. I need it to return an error if the date isn't correct, is there anyway to do this? The code is pasted below:

Private Sub TextBox1_afterupdate()

If IsDate(TextBox1) Then
TextBox1.Value = Format(TextBox1.Value, "DD-MMM-YY")
Sheets("sheet1").Range("i2") = CDate(TextBox1)
End If
End Sub

Once this has processed it runs through certain validations below, so ideally I'd like ElseIf not IsDate to pick up that it isn't a valid date.:

ElseIf TextBox1 = "" Then
MsgBox "You must enter a Start date", _
vbOKOnly + vbCritical, "Entry Error"
TextBox1.SetFocus
Exit Sub
ElseIf Not IsDate(TextBox1) Then
TextBox1.Value = ""
MsgBox "Please enter a valid date", vbOKOnly + vbCritical, "Entry Error"
TextBox1.SetFocus
Exit Sub
ElseIf Range("i2") < Date Then
TextBox1.Value = ""
MsgBox "The start date you've entered is in the past", _
vbOKOnly + vbCritical, "Entry Error"
TextBox1.SetFocus
Exit Sub
ElseIf Range("i2") > Range("d3") Then
TextBox1.Value = ""
MsgBox "The start date you've entered is more than a year in advance", _
vbOKOnly + vbCritical, "Entry Error"
TextBox1.SetFocus
Exit Sub
thanks

[dotchiejack]

Hi,
Try this
Go to the textbox properties and in the tag field fill in the word date.
And use this in your code.

Please Login or Register  to view this content.

[GraingerT]

Hi

Thanks for the reply and advice. I've tried this, but I receive a runtime error:

Run-time error '91'

Object variable or With block variable not set.

When I select debug it opens on 'If LCase(Right(ct.Tag, 4)) = "date" Then'

Is there something else I need to add elsewhere in the code. I've pasted the modified code below:

Private Sub TextBox1_afterupdate()

Dim ct As Control
If LCase(Right(ct.Tag, 4)) = "date" Then
If Not IsDate(ct) Then
MsgBox ct.Tag & " is not a valid date!!", vbCritical, "Error!"
ct.SetFocus
Exit Sub
End If

If IsDate(TextBox1) Then
TextBox1.Value = Format(TextBox1.Value, "DD-MMM-YY")
Sheets("sheet1").Range("i2") = CDate(TextBox1)
End If
End If
End Sub

_______________

Below is the full userform code as it appears in the workbook if that's any help:


Option Explicit

Private Sub ComboBox1_Change()

End Sub

Private Sub EndDate_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)

End Sub

Private Sub StartDate_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)

End Sub
Option Explicit





End Sub



Private Sub EnterRequest_Click()
' Make sure Sheet1 is active.
Sheets("Sheet1").Activate

' Check to confirm all required input feilds have been filled in.
' If error set focus to missing data input field.

If NumberDays = 0 And Toil = 0 Then
MsgBox "Please enter the number of Days or hours Toil required", _
vbOKOnly + vbCritical, "Entry Error"
NumberDays.SetFocus
Exit Sub
ElseIf NumberDays = "" Then
MsgBox "You must enter the number of Days requested", _
vbOKOnly + vbCritical, "Entry Error"
NumberDays.SetFocus
Exit Sub
ElseIf Toil = "" Then
MsgBox "You must enter required number of hours toil", _
vbOKOnly + vbCritical, "Entry Error"
Toil.SetFocus
Exit Sub
ElseIf TextBox1 = "" Then
MsgBox "You must enter a Start date", _
vbOKOnly + vbCritical, "Entry Error"
TextBox1.SetFocus
Exit Sub
ElseIf Not IsDate(TextBox1) Then
TextBox1.Value = ""
MsgBox "Please enter a valid date", vbOKOnly + vbCritical, "Entry Error"
TextBox1.SetFocus
Exit Sub
ElseIf Range("i2") < Date Then
TextBox1.Value = ""
MsgBox "The start date you've entered is before today", _
vbOKOnly + vbCritical, "Entry Error"
TextBox1.SetFocus
Exit Sub
ElseIf Range("i2") > Range("d3") Then
TextBox1.Value = ""
MsgBox "The start date you've entered is more than a year in advance", _
vbOKOnly + vbCritical, "Entry Error"
TextBox1.SetFocus
Exit Sub
ElseIf TextBox2 = "" Then
MsgBox "You must enter an End date", _
vbOKOnly + vbCritical, "Entry Error"
TextBox2.SetFocus
Exit Sub
ElseIf Not IsDate(TextBox2) Then
MsgBox "Please enter a valid date", vbOKOnly + vbCritical, "Entry Error"
TextBox2.Value = ""
TextBox2.SetFocus
Exit Sub

ElseIf Range("i3") > Range("b1") Then
TextBox2.Value = ""
MsgBox "The end date you've entered is more than a year & 3 weeks in advance", _
vbOKOnly + vbCritical, "Entry Error"
TextBox2.SetFocus
Exit Sub
ElseIf ComboBox2 = "" Then
MsgBox "You must state whether a request or a cancellation", _
vbOKOnly + vbCritical, "Entry Error"
TextBox2.SetFocus
Exit Sub

ElseIf Range("i3") < Range("i2") Then
MsgBox "The end date you have selected is earlier than the start date", _
vbOKOnly + vbCritical, "Entry Error"
TextBox2.Value = ""
TextBox2.SetFocus
Exit Sub
' ElseIf Comments = "" Then
' MsgBox "You must enter a Comment", _
' vbOKOnly + vbCritical, "Entry Error"
' Comment.SetFocus
' Exit Sub


End If


' If answered "Yes" then proceed to insert new line (via macro "NewEntry").
Call NewEntry

' Then enter data from UserForm1.
Range("$B$10").Value = ComboBox1.Text
Range("$e$10").Value = ComboBox2.Text
Range("$f$10").Value = NumberDays.Text
Range("$g$10").Value = Toil.Text
Sheets("sheet1").Range("h10") = CDate(TextBox1)
Sheets("sheet1").Range("i10") = CDate(TextBox2)
Range("$j$10").Value = Comments.Text

' Check to see if another entery is required?.
If MsgBox("Request has been added" & vbNewLine & vbNewLine & "Would you like to add another Request?", _
vbYesNo + vbQuestion, "Action Entry") = vbYes Then
Call Clear_Form
Else
Unload UserForm1
' Reprotect spreadsheet by calling the 'Protect' macro
Call Protect
End If

End Sub

Private Sub Clear_Form()
' Clear the controls for the next entry.
' RequestedBy = ""
NumberDays = "0"
Toil = "0"
Comments = ""

' Set focus for next entry at the "Action Type" input field.
NumberDays.SetFocus

End Sub

Private Sub Cancel_Click()
' Check to see if cancel entry is correct?.
If MsgBox("Are you sure you want to cancel this Request?", _
vbYesNo + vbQuestion, "Cancel Confirmation") = vbYes Then
Unload UserForm1
' Reprotect spreadsheet by calling the 'Protect' macro
Call Protect
End If

End Sub






Private Sub TextBox1_afterupdate()

Dim ct As Control
If LCase(Right(ct.Tag, 4)) = "date" Then
If Not IsDate(ct) Then
MsgBox ct.Tag & " is not a valid date!!", vbCritical, "Error!"
ct.SetFocus
Exit Sub
End If

If IsDate(TextBox1) Then
TextBox1.Value = Format(TextBox1.Value, "DD-MMM-YY")
Sheets("Sheet1").Unprotect Password:="honkers"
Sheets("sheet1").Range("i2") = CDate(TextBox1)
End If
End If
End Sub

Private Sub TextBox2_afterupdate()

If IsDate(TextBox2) Then
TextBox2.Value = Format(TextBox2.Value, "DD-MMM-YY")
Sheets("Sheet1").Unprotect Password:="honkers"
Sheets("sheet1").Range("i3") = CDate(TextBox2)
End If
End Sub


Private Sub UserForm_Click()

End Sub