Format to mm/yy; Not dd/mm/yy in VBA userform

[rahulbassi268]

Hello All,

I am building a userform for pharma data entry. The Expiry date is actually just a month & year. Not a date in actual.
So, I have a textbox in userform named Expiry_TxtBox.
I have below requirements:

1. If user enters 4 characters like 0525, it should get itself modified to 05/25 (It must retain leading zero too)
2. If user enters slash like 08/26, system should accept it as such.

I am using below snippet, but doesn't work properly. Could use some ideas.

Private Sub Expiry_TxtBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)

   Expiry_TxtBox.Text = Format$(Expiry_TxtBox.Text, "##/##")

End Sub

 

[Akuini]

Maybe this:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox1
    If .Text Like "####" Then
        .Text = Left(.Text, 2) & "/" & Right(.Text, 2)
    ElseIf .Text Like "##/##" Then
        'do nothing
    Else
        MsgBox "Wrong input"
        .Text = ""
        Beep
    End If
End With
End Sub

 

[rahulbassi268]

This worked.
Thanks.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.
And you can add Cancel = True, so if the user enter a wrong input the focus will stay in the textbox. Like this:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox1
    If .Text Like "####" Then
        .Text = Left(.Text, 2) & "/" & Right(.Text, 2)
    ElseIf .Text Like "##/##" Then
        'do nothing
    Else
        MsgBox "Wrong input"
        Cancel = True
        .Text = ""
        Beep
    End If
End With
End Sub