none
How to have a date mask in excel's VBA? RRS feed

  • Question

  • Simple,

    our client want to have a date mask as

    DD/MM/YYYY mask in the text box

    I can validate if the values are fine easily.

    However is there a way to have a mask showing

    __/__/____

    so use can only input numbers on the _ ?

    Normally it works in Access vba but this seems to be nowhere to be found in Excel vba.

    Monday, April 9, 2018 12:15 AM

Answers

  • Hi Okol-Gui,

    You had mentioned that,"I can validate if the values are fine easily.However is there a way to have a mask showing.so use can only input numbers on the _ ?"

    I find one example from old thread and try to modify it as per your requirement.

    You can try to refer code below.

    Dim NewString, MyString, mask As String
    Dim position, pos As Variant
    
    
    
    Private Sub TextBox1_Change()
    If IsNumeric(Right(TextBox1.Text, 2)) And Len(TextBox1.Text) >= 11 Then
        TextBox1.Text = Left(TextBox1.Text, Len(TextBox1.Text) - 1)
    Else
        position = TextBox1.SelStart
        MyString = TextBox1.Text
        pos = InStr(1, MyString, "_")
    If pos > 0 Then
        NewString = Left(MyString, pos - 1)
    Else
        NewString = MyString
    End If
    If Len(NewString) < 11 Then
        TextBox1.Text = NewString & Right(mask, Len(mask) - Len(NewString))
        TextBox1.SelStart = Len(NewString)
    End If
    End If
    If Len(TextBox1.Text) >= 11 Then
         TextBox1.Text = Left(TextBox1.Text, 10)
    End If
    
    
    End Sub
    
    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim strDate As String
    strDate = Me.TextBox1
    If IsDate(strDate) Then
        strDate = Format(CDate(strDate), "DD/MM/YYYY")
        MsgBox strDate
      Else
        MsgBox "Wrong date format"
      End If
    End Sub
    
    Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    position = TextBox1.SelStart
    If KeyCode = 8 Then
        TextBox1.Text = mask
    End If
    End Sub
    Private Sub UserForm_Initialize()
    TextBox1.SelStart = 0
    mask = "__/__/____"
    TextBox1.Text = mask
    End Sub
    

    Output:

    Reference:

    Text Box With Input Mask

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Okol-Gui Tuesday, April 10, 2018 1:58 PM
    Tuesday, April 10, 2018 2:27 AM
    Moderator

All replies

  • unfortunately format is not correct and client really want a textbox to juste write the date with a mask
    Monday, April 9, 2018 3:53 PM
  • Hi Okol-Gui,

    You had mentioned that,"I can validate if the values are fine easily.However is there a way to have a mask showing.so use can only input numbers on the _ ?"

    I find one example from old thread and try to modify it as per your requirement.

    You can try to refer code below.

    Dim NewString, MyString, mask As String
    Dim position, pos As Variant
    
    
    
    Private Sub TextBox1_Change()
    If IsNumeric(Right(TextBox1.Text, 2)) And Len(TextBox1.Text) >= 11 Then
        TextBox1.Text = Left(TextBox1.Text, Len(TextBox1.Text) - 1)
    Else
        position = TextBox1.SelStart
        MyString = TextBox1.Text
        pos = InStr(1, MyString, "_")
    If pos > 0 Then
        NewString = Left(MyString, pos - 1)
    Else
        NewString = MyString
    End If
    If Len(NewString) < 11 Then
        TextBox1.Text = NewString & Right(mask, Len(mask) - Len(NewString))
        TextBox1.SelStart = Len(NewString)
    End If
    End If
    If Len(TextBox1.Text) >= 11 Then
         TextBox1.Text = Left(TextBox1.Text, 10)
    End If
    
    
    End Sub
    
    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim strDate As String
    strDate = Me.TextBox1
    If IsDate(strDate) Then
        strDate = Format(CDate(strDate), "DD/MM/YYYY")
        MsgBox strDate
      Else
        MsgBox "Wrong date format"
      End If
    End Sub
    
    Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    position = TextBox1.SelStart
    If KeyCode = 8 Then
        TextBox1.Text = mask
    End If
    End Sub
    Private Sub UserForm_Initialize()
    TextBox1.SelStart = 0
    mask = "__/__/____"
    TextBox1.Text = mask
    End Sub
    

    Output:

    Reference:

    Text Box With Input Mask

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Okol-Gui Tuesday, April 10, 2018 1:58 PM
    Tuesday, April 10, 2018 2:27 AM
    Moderator