none
how can format date in textbox userform vba RRS feed

  • Question

  • dears

    good greeting

    how can format date by automatic way just when i'm write it inside textbox excel userform vba

    regards.......

    Monday, February 26, 2018 7:23 PM

Answers

  • Hello TAREK SHARAF,

    Textbox in useform does not have format property, you could format its text content in AfterUpated event.

    Here is the simply code and demonstration.

    Private Sub TextBox1_AfterUpdate()
    If IsDate(Me.TextBox1.Text) Then
    Me.TextBox1.Text = Format(Me.TextBox1.Text, "dd/mm/yyyy")
    End If
    End Sub
    
    

    Best Regards,

    Terry


    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 TAREK SHARAF Tuesday, February 27, 2018 4:48 AM
    Tuesday, February 27, 2018 3:13 AM
  • Hi,

    It is hard to change date format automatically by handling every single key-input in a TextBox.
    And I'm afraid you forget what date format should be used.
    So, I made a button [Change Format] that changed date format according to selected an option button (radio button).    

    Here's my code:
    ' ---[Change Format] button
    Private Sub btn_ChangeFormat_Click()
        If (Len(TextBox1.Text) < 8) _
            Or (InStr(TextBox1.Text, "/") < 2) Then
            MsgBox "invalid date value"
            Exit Sub
        End If
        ' ---------------
        If (rbt_YMD_S.Value = True) Then
            TextBox1.Text = Format(TextBox1.Text, "yyyy/M/d")
        End If
        If (rbt_DMY_S.Value = True) Then
            TextBox1.Text = Format(TextBox1.Text, "d/M/yyyy")
        End If
        If (rbt_MDY_S.Value = True) Then
            TextBox1.Text = Format(TextBox1.Text, "M/d/yyyy")
        End If
        ' ---------------
        If (rbt_YMD_L.Value = True) Then
            TextBox1.Text = Format(TextBox1.Text, "yyyy/MMM/dd")
        End If
        If (rbt_DMY_L.Value = True) Then
            TextBox1.Text = Format(TextBox1.Text, "dd/MMM/yyyy")
        End If
        If (rbt_MDY_L.Value = True) Then
            TextBox1.Text = Format(TextBox1.Text, "MMM/dd/yyyy")
        End If
    End Sub

    Regards,

    Ashidacchi

    • Edited by Ashidacchi Tuesday, February 27, 2018 3:50 AM
    • Marked as answer by TAREK SHARAF Tuesday, February 27, 2018 4:48 AM
    Tuesday, February 27, 2018 3:49 AM

All replies

  • Hello TAREK SHARAF,

    Textbox in useform does not have format property, you could format its text content in AfterUpated event.

    Here is the simply code and demonstration.

    Private Sub TextBox1_AfterUpdate()
    If IsDate(Me.TextBox1.Text) Then
    Me.TextBox1.Text = Format(Me.TextBox1.Text, "dd/mm/yyyy")
    End If
    End Sub
    
    

    Best Regards,

    Terry


    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 TAREK SHARAF Tuesday, February 27, 2018 4:48 AM
    Tuesday, February 27, 2018 3:13 AM
  • Hi,

    It is hard to change date format automatically by handling every single key-input in a TextBox.
    And I'm afraid you forget what date format should be used.
    So, I made a button [Change Format] that changed date format according to selected an option button (radio button).    

    Here's my code:
    ' ---[Change Format] button
    Private Sub btn_ChangeFormat_Click()
        If (Len(TextBox1.Text) < 8) _
            Or (InStr(TextBox1.Text, "/") < 2) Then
            MsgBox "invalid date value"
            Exit Sub
        End If
        ' ---------------
        If (rbt_YMD_S.Value = True) Then
            TextBox1.Text = Format(TextBox1.Text, "yyyy/M/d")
        End If
        If (rbt_DMY_S.Value = True) Then
            TextBox1.Text = Format(TextBox1.Text, "d/M/yyyy")
        End If
        If (rbt_MDY_S.Value = True) Then
            TextBox1.Text = Format(TextBox1.Text, "M/d/yyyy")
        End If
        ' ---------------
        If (rbt_YMD_L.Value = True) Then
            TextBox1.Text = Format(TextBox1.Text, "yyyy/MMM/dd")
        End If
        If (rbt_DMY_L.Value = True) Then
            TextBox1.Text = Format(TextBox1.Text, "dd/MMM/yyyy")
        End If
        If (rbt_MDY_L.Value = True) Then
            TextBox1.Text = Format(TextBox1.Text, "MMM/dd/yyyy")
        End If
    End Sub

    Regards,

    Ashidacchi

    • Edited by Ashidacchi Tuesday, February 27, 2018 3:50 AM
    • Marked as answer by TAREK SHARAF Tuesday, February 27, 2018 4:48 AM
    Tuesday, February 27, 2018 3:49 AM
  • thanks too much my dear terry

     But I also want to put a error message if the user has entered any text in the textbox other than the date

    and i want  from the textbox to appear the georgian date or hijrian date as it when import date from sheet don't change date format

    regards..............


    Tuesday, February 27, 2018 4:55 AM
  • thanks too much my dear terry

     But I also want to put a error message if the user has entered any text in the textbox other than the date

    and i want  from the textbox to appear the georgian date or hijrian date as it when import date from sheet don't change date format

    regards..............

    Tuesday, February 27, 2018 7:35 PM
  • Hello TARKE SHARAF,

    >>But I also want to put a error message if the user has entered any text in the textbox other than the date

    Private Sub TextBox1_AfterUpdate()
    If IsDate(Me.TextBox1.Text) Then
    Me.TextBox1.Text = Format(Me.TextBox1.Text, "dd/mm/yyyy")
    Else
    MsgBox "Please enter a date string!"
    End If
    End Sub

    >>i want  from the textbox to appear the georgian date or hijrian date as it

    We support one issue for one thread and this is more related to VBA instead of Excel Object Model. So I would suggest you post a thread in Visual Basic for Applications (VBA) for help. 

    Thanks for understanding.

    Best Regards,

    Terry



    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.

    Wednesday, February 28, 2018 8:07 AM