Format & Find Date Added To TextBox

  • I have a log that I am trying to make compatible for international users. They enter bike rides via a form. When the user hits submit Excel finds the date and then posts the ride info.


    The problem is that if I format the date textbox (textbox1) as international the date inputted in textbox1 isn't being found in the column.


    Here are the snippets of code from the form (other parts of the code were excluded because they shouldn't effect this issue.


    Code when the form initializes

    Code
    Private Sub UserForm_Initialize()
        If wb.Worksheets("Admin").Range("Custom_Date_Format") = "mm/dd/yyyy" Then
            TextBox1.Value = Format(Date, "mm/dd/yyyy")
        ElseIf wb.Worksheets("Admin").Range("Custom_Date_Format") = "dd/mm/yyyy" Then
            TextBox1.Value = Format(Date, "dd/mm/yyyy")
        End If
    End Sub

    Code part way through when submit button is pressed. This code matches the dates and tries to find the corresponding date. IF the date is inputed in international format (dd/mm/yyyy) and the text box is formatted as "TextBox1.Value = Format(Date, "dd/mm/yyyy")" and the Date_Col is formatted dd/mm/yyyy why can't the date in textbox1 match the dates in Date_Col. What it does match in this case is the US date format. For instance. I enter 12/01/2009 and it finds Dec 1 when I want it to find Jan 12, 2009.


    Date_Col is formatted using one of these depending on what the user picks...

    Code
    Sub Date_Change_Intl()
        Set rngBB = ThisWorkbook.Worksheets("2009 Log").Range("B:B")
        rngBB.NumberFormat = "dd/mm/yyyy;@"
        Worksheets("Admin").Range("Custom_Date_Format") = "dd/mm/yyyy"
    End Sub
    Sub Date_Change_US()
        Set rngBB = ThisWorkbook.Worksheets("2009 Log").Range("B:B")
        rngBB.NumberFormat = "dddd dd;@"
        Worksheets("Admin").Range("Custom_Date_Format") = "mm/dd/yyyy"
    End Sub

    Any help why this code doesn't work?


    Guy

  • Re: Date Formating Of Text Box And Matching In Spreadsheet


    Using the Find Method to locate dates is tricky for non-US date settings. See Find Dates


    To ensure dates entered into a TextBox Control are formatted as European format (d m y) use DateSerial. E.g


    Code
    If IsDate(TextBox1) Then
       TextBox1=Format(DateSerial(Year(TextBox1),Month(TextBox1),Day(TextBox1)),"dd/mm/yyyy")
    End If
  • Re: Format & Find Date Added To TextBox


    Dave, I never used DateSerial before. I will look into that more. To make the code work in my workbook I added a check to make sure the user preference is indeed dd/mm/yyyy. I think it works (at least a couple of trial worked so far. Thanks for your assistance).


    Code
    If Worksheets("Admin").Range("Custom_Date_Format") = "dd/mm/yyyy" Then
            If IsDate(TextBox1) Then
                TextBox1 = Format(DateSerial(Year(TextBox1), Month(TextBox1), Day(TextBox1)), "dd/mm/yyyy")
            End If
        End If
  • Re: Format & Find Date Added To TextBox


    I THINK you need

    Code
    Range("Custom_Date_Format").[B]NumberFormat[/B] = "dd/mm/yyyy"

    You shouldn't need the sheet name unless you have sheet level names the same, or referencing from a Sheet Module of another Worksheet.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!