USERFORM VBA Textbox Format Date and Time

[acombest]

How would I go about making a date text box with 6 digits and permanent / / format so all that needs to be entered for todays date for example is 051517.
In addition I also need a 4 digit textbox with a : that is formatted for military time. I have gone through many of the options and I found how to format it but nothing I have found allows for a floating : nor have I found the format i want.

Any help would be greatly appreciated,
Thanks

 

[Leith Ross]

Hello acombest,

Doing this is a bit complex. You basically have to construct a parser based on the TextBox's events.

The code below is for the sample workbook that can be downloaded by clicking Here

This code formats the date as dd/mm/yy and the time as hh:mm in 24 hour form as you type in the numbers.

Private Sub TextBox1_Change()


    Dim ascii   As Integer
    Dim d       As Integer
    Dim m       As Integer
    Dim n       As Integer
    
        n = Len(TextBox1.Value)
        
        If n = 0 Then Exit Sub
        
            ascii = Asc(Right(TextBox1.Value, 1))
            
            ' Validate the day is 2 digits from 01 to 31.
            If n = 2 And ascii <> 47 And CharCode <> 8 Then
                d = CInt(Left(TextBox1.Value, 2))
                If d < 1 Or d > 31 Then
                    MsgBox "Invalid day number " & d & vbLf & "Days are from 01 to 31."
                    TextBox1.Value = ""
                    Exit Sub
                End If
            End If
            
            ' Validate the month is 2 digits from 01 to 12.
            If n = 5 And ascii <> 47 And CharCode <> 8 Then
                m = CInt(Mid(TextBox1.Value, 4, 2))
                If m < 1 Or m > 12 Then
                    MsgBox "Invalid month number " & m & vbLf & "Months are from 01 to 12."
                    TextBox1.Value = Left(TextBox1.Value, 3)
                End If
            End If
                      
End Sub


Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)


        CharCode = 0
        
        Select Case KeyCode
            Case 8, 10, 13, 46: CharCode = KeyCode  ' Process these control characters: BS, LF, CR and Delete.
            Case 48 To 57, 96 To 105, 111, 191      ' Display numbers and forward slahes from keyboard and number pad.
            Case Else: KeyCode = 0                  ' Erase all other input.
        End Select
        
End Sub


Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)


    Dim char    As String
    Dim n       As Integer
    
        n = Len(TextBox1)
        
        If n > 0 Then char = Right(TextBox1, 1)
        
        Select Case KeyCode
            Case 48 To 57, 96 To 105
                ' Automatically add the forward slash after the day and month.
                If n = 3 Or n = 6 Then
                    TextBox1.Value = Left(TextBox1.Value, n - 1) & "/" & char
                End If
            Case 111, 191
                ' Display the forward slash only after the day or month.
                If n <> 3 Or n <> 6 Then
                    TextBox1.Value = Left(TextBox1.Value, n - 1)
                End If
        End Select
        
End Sub


Private Sub TextBox2_Change()


    Dim ascii   As Integer
    Dim h       As Integer
    Dim m       As Integer
    Dim n       As Integer
    
        n = Len(TextBox2.Value)
        
        If n = 0 Then Exit Sub
        
            ascii = Asc(Right(TextBox2.Value, 1))
            ' Validate the hour is 2 digits from 01 to 23.
            If n = 2 And ascii <> 58 And CharCode <> 8 Then
                h = CInt(Left(TextBox2.Value, 2))
                If h < 0 Or h > 23 Then
                    MsgBox "Invalid hour " & h & vbLf & "Hours are from 00 to 23."
                    TextBox1.Value = ""
                    Exit Sub
                End If
            End If
            
            ' Validate the month is 2 digits from 01 to 12.
            If n = 5 And ascii <> 58 And CharCode <> 8 Then
                m = CInt(Mid(TextBox2.Value, 4, 2))
                If m < 0 Or m > 59 Then
                    MsgBox "Invalid minute " & m & vbLf & "Minutes are from 00 to 59."
                    TextBox2.Value = Left(TextBox2.Value, 3)
                End If
            End If


End Sub


Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)


        CharCode = 0
        n = KeyCode
        
        Select Case KeyCode
            Case 8, 10, 13, 46: CharCode = KeyCode  ' Process these control characters: BS, LF, CR and Delete.
            Case 48 To 57, 96 To 105                ' Display numbers and forward slahes from keyboard and number pad.
            Case 186 And Shift = 1                  ' Display the colon.
            Case Else: KeyCode = 0                  ' Erase all other input.
        End Select


End Sub


Private Sub TextBox2_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)


    Dim char    As String
    Dim n       As Integer
    
        n = Len(TextBox2)
        
        If n > 0 Then char = Right(TextBox2, 1)
        
        Select Case KeyCode
            Case 48 To 57, 96 To 105
                ' Automatically add the colon after the day and month.
                If n = 3 Then
                    TextBox2.Value = Left(TextBox2.Value, n - 1) & ":" & char
                End If
            Case 183 And Shift = 1
                ' Display the colon only after the minutes.
                If n <> 3 Then
                    TextBox2.Value = Left(TextBox2.Value, n - 1)
                End If
        End Select


End Sub