Add another component for a requirement for a txt box on user form.

[Livin404]

Greetings,

I'm quite happy I was able to sort out a code to ensure my date box is not left blank. What I would like is to make sure the box is not blank or the user doesn't type any rubbish in to get past the requirement. I would love to have this be an or type statement it can't be blank and it has to read in a "dd mmm yy" format.

The existing code is:

Private Sub TxtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Trim(TxtDate.Value) = "" And Me.Visible Then
MsgBox "Please enter date in dd mmm yy format", vbCritical, "Error"
Cancel = True
InboundForm.TxtDate.SetFocus
TxtDate.BackColor = vbYellow
Else
TxtDate.BackColor = vbWhite
End If
End Sub

Thank you very much indeed.

 

[breynolds0431]

Greetings,

I'm quite happy I was able to sort out a code to ensure my date box is not left blank. What I would like is to make sure the box is not blank or the user doesn't type any rubbish in to get past the requirement. I would love to have this be an or type statement it can't be blank and it has to read in a "dd mmm yy" format.

The existing code is:

Private Sub TxtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Trim(TxtDate.Value) = "" And Me.Visible Then
MsgBox "Please enter date in dd mmm yy format", vbCritical, "Error"
Cancel = True
InboundForm.TxtDate.SetFocus
TxtDate.BackColor = vbYellow
Else
TxtDate.BackColor = vbWhite
End If
End Sub

Thank you very much indeed.

Hi. Here are a couple options.

Option #1: I'm not sure if you can validate if a format of "dd mmm yy" was entered with the blank spaces in between the day month and year. If you can allow a format of "ddMmmyy", then this option should work for you:

Private Sub TxtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)

With Me
    If .Visible And Trim(.TxtDate.Value) = "" Or .TxtDate <> Format(.TxtDate, "dd Mmm yy") Then
        MsgBox "Please enter date in ddMmmyy format", vbCritical, "Error"
        Cancel = True
        'InboundForm.TxtDate.SetFocus
        .TxtDate.BackColor = vbYellow
    Else
        .TxtDate = UCase(.TxtDate)
        .TxtDate.BackColor = vbWhite
    End If
End With
End Sub

Private Sub TxtDate_KeyPress(KeyAscii As Integer)
'add this to prevent spaces in the text box
    If KeyAscii = 32 Then
        KeyAscii = 0
    End If
End Sub

Option #2: Have more control over the type of data being entered in the text box by only allowing numbers in a "YYYYMMDD" format where you can then update to your liking of "dd Mmm yy"

Private Sub TxtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)

'Will exit if Me not visible or TxtDate is blank
If Not Me.Visible Or TxtDate.Value = "" Then Exit Sub

'Will require 8 numbers (note the keypress sub to only allow numbers)
If Len(TxtDate.Value) <> 8 Then
    MsgBox "Please enter date in YYYYMMDD format.", vbInformation, "Invalid Date"
    Cancel = True
    'InboundForm.TxtDate.SetFocus
    TxtDate.BackColor = vbYellow
    Exit Sub
End If

'Verifies month and days are acceptable
If Mid(TxtDate.Value, 5, 2) > 12 Or _
    Right(TxtDate.Value, 2) > 31 Then
   
    MsgBox "Please enter date in YYYYMMDD format.", vbInformation, "Invalid Date"
    Cancel = True
    'InboundForm.TxtDate.SetFocus
    TxtDate.BackColor = vbYellow
    Exit Sub
End If

'Converts YYYYMMDD to dd Mmm yr format
Dim yr As Long: yr = Left(TxtDate.Value, 4)
Dim mo As String: mo = MonthName(Mid(TxtDate.Value, 5, 2), True)
Dim da As Long: da = Right(TxtDate.Value, 2)

With TxtDate
    .BackColor = vbWhite
    .Value = da & " " & mo & " " & yr
End With

End Sub

Private Sub TxtDate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'Will limit date text box to number input only
If KeyAscii > Asc("9") Or KeyAscii < Asc("0") Then
    If KeyAscii = Asc("-") Then
        If InStr(1, Me.TxtDate.Text, "-") > 0 Or _
           Me.TxtDate.SelStart > 0 Then KeyAscii = 0
    ElseIf KeyAscii = Asc(".") Then
        If InStr(1, Me.TxtDate.Text, ".") > 0 Then KeyAscii = 0
    Else
        KeyAscii = 0
    End If
End If
End Sub

 

[Livin404]

Hi. Here are a couple options.

Option #1: I'm not sure if you can validate if a format of "dd mmm yy" was entered with the blank spaces in between the day month and year. If you can allow a format of "ddMmmyy", then this option should work for you:

Private Sub TxtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)

With Me
    If .Visible And Trim(.TxtDate.Value) = "" Or .TxtDate <> Format(.TxtDate, "dd Mmm yy") Then
        MsgBox "Please enter date in ddMmmyy format", vbCritical, "Error"
        Cancel = True
        'InboundForm.TxtDate.SetFocus
        .TxtDate.BackColor = vbYellow
    Else
        .TxtDate = UCase(.TxtDate)
        .TxtDate.BackColor = vbWhite
    End If
End With
End Sub

Private Sub TxtDate_KeyPress(KeyAscii As Integer)
'add this to prevent spaces in the text box
    If KeyAscii = 32 Then
        KeyAscii = 0
    End If
End Sub

Option #2: Have more control over the type of data being entered in the text box by only allowing numbers in a "YYYYMMDD" format where you can then update to your liking of "dd Mmm yy"

Private Sub TxtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)

'Will exit if Me not visible or TxtDate is blank
If Not Me.Visible Or TxtDate.Value = "" Then Exit Sub

'Will require 8 numbers (note the keypress sub to only allow numbers)
If Len(TxtDate.Value) <> 8 Then
    MsgBox "Please enter date in YYYYMMDD format.", vbInformation, "Invalid Date"
    Cancel = True
    'InboundForm.TxtDate.SetFocus
    TxtDate.BackColor = vbYellow
    Exit Sub
End If

'Verifies month and days are acceptable
If Mid(TxtDate.Value, 5, 2) > 12 Or _
    Right(TxtDate.Value, 2) > 31 Then
  
    MsgBox "Please enter date in YYYYMMDD format.", vbInformation, "Invalid Date"
    Cancel = True
    'InboundForm.TxtDate.SetFocus
    TxtDate.BackColor = vbYellow
    Exit Sub
End If

'Converts YYYYMMDD to dd Mmm yr format
Dim yr As Long: yr = Left(TxtDate.Value, 4)
Dim mo As String: mo = MonthName(Mid(TxtDate.Value, 5, 2), True)
Dim da As Long: da = Right(TxtDate.Value, 2)

With TxtDate
    .BackColor = vbWhite
    .Value = da & " " & mo & " " & yr
End With

End Sub

Private Sub TxtDate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'Will limit date text box to number input only
If KeyAscii > Asc("9") Or KeyAscii < Asc("0") Then
    If KeyAscii = Asc("-") Then
        If InStr(1, Me.TxtDate.Text, "-") > 0 Or _
           Me.TxtDate.SelStart > 0 Then KeyAscii = 0
    ElseIf KeyAscii = Asc(".") Then
        If InStr(1, Me.TxtDate.Text, ".") > 0 Then KeyAscii = 0
    Else
        KeyAscii = 0
    End If
End If
End Sub

Thank you so much for the input. I am looking more to the first option. I'm trying to sort out few other loose strings of my overall code, then I will pivot back to this one.