Macro problem with Excel - Date format

Question

Hi,

I've have this code, below, which inserts today's date in a cell from a text box, but it shows the date as US date format when I need it the UK format.

TextBoxDay works fine, it inserts the cell B10 as it should, and keeps the cell in the "General" format.

But, when "WorkingDay" is equal to "TextBoxDay" the cell B11 shows the date in the US format, and also changes the cell to date format.

So basically, if I type in the text box "TextBoxDay" 05/06/2013 (UK format), the result in B11 is 06/05/2013 and also changes the cell to a date format rather keeping it as General.

Private Sub CheckBoxTodaysDate_Click()

TextBoxDay.Text = Format(Date, "dd/mm/yyyy")

TextBoxDay.Text = Format(DateValue(TextBoxDay.Text), "dd/mm/yyyy")

If CheckBoxTodaysDate = True Then

TextBoxDay.Locked = True

Else

TextBoxDay.Locked = False

End If

WorkingDay = TextBoxDay

' Test WorkingDay

Range("B10") = TextBoxDay

Range("B11") = WorkingDay

End Sub

Hope someone can help,

Thanks

Scott

Answer 1

Hi,

Text boxes return text and not dates so in your code both B10 would be a text value and not a date as well as the problems with B11. Try it this way.

Private Sub CheckBoxTodaysDate_Click()

TextBoxDay.Text = Format(Date, "dd/mm/yyyy")

TextBoxDay.Text = Format(DateValue(TextBoxDay.Text), "dd/mm/yyyy")

If CheckBoxTodaysDate = True Then

    TextBoxDay.Locked = True

    Else

        TextBoxDay.Locked = False

    End If

WorkingDay = TextBoxDay

    ' Test WorkingDay

    With Range("B10")

        .NumberFormat = "DD/MM/YYYY"

        .Value = CDate(TextBoxDay)

    End With

    With Range("B11")

        .NumberFormat = "DD/MM/YYYY"

       .Value = CDate(WorkingDay)

   End With

End Sub

Answer 2

Thanks very much Mike, works perfectly.

Best regards

Scott