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 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.
Dim Date_Col, Date_Row, Last_Row, I, Selected_Row As Integer Dim Selected_Date As Date Dim ws As Worksheet Set ws = Sheets("2009 Log") Date_Col = Worksheets("2009 Log").Range("column_h_date").Column Date_Row = Worksheets("2009 Log").Range("column_h_date").Row Last_Row = 1500 Selected_Date = TextBox1.Value Worksheets("2009 Log").Select For I = Date_Row + 1 To Last_Row If (Cells(I, Date_Col) = Selected_Date) Then Cells(I, Date_Col).Select If (Cells(I, Date_Col) = Selected_Date) Then Selected_Row = I If (Cells(I, Date_Col) = Selected_Date) Then Exit For Next I
Date_Col is formatted using one of these depending on what the user picks...
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?