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
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.
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
Display More
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?
Guy