Userform date format

[mopey12345]

Hi
Help needed please. I have tried various tips but still struggling to stop the input userform txt_start_date_box dd/mm/yy writing to the spreadsheet and changing the date format to mm/dd./yy
eg. Range("A2").Value = Format(Format(txt_start_date.Value, "Long Date"), "DD/MM/YYYY"). Tried ,value and .text but no luck. Any help appreciated. Thanks Phil

 

[dmt32]

Hi,
try using CDate type conversation function

Range("A2").Value = CDate(Me.txt_start_date.Value)

Dave

 

[mopey12345]

Hi,
try using CDate type conversation function

Range("A2").Value = CDate(Me.txt_start_date.Value)

Dave

Thanks that worked great. Phil

 

[dmt32]

Thanks that worked great. Phil

You are welcome but do be aware, that CDate function will error if textbox does not contain a valid Date.

Dave

 

[mopey12345]

You are welcome but do be aware, that CDate function will error if textbox does not contain a valid Date.

Dave

Thanks again. I have been trying for a couple of hours.

 

[dmt32]

Thanks again. I have been trying for a couple of hours.

welcome - if really stuck just post to forum, plenty here to offer guidance.

Dave

 

[Siddharth Rout]

Thanks again. I have been trying for a couple of hours.

And hence I never recommend using Textboxes to input dates. You may want to use this Calendar Form?

 

[mopey12345]

And hence I never recommend using Textboxes to input dates. You may want to use this Calendar Form?

Thanks I'll have a look.

 

[mopey12345]

And hence I never recommend using Textboxes to input dates. You may want to use this Calendar Form?

Hi, had a look at the calendar solution. Is the best way to include a calendar on the userform to select the date.? If I struggle with that, is there any other alternative to textboxes? Thanks Phil

 

[dmt32]

I struggle with that, is there any other alternative to textboxes? Thanks Phil

As an idea You could add a combobox & fill it with dates for say current month

example

Private Sub UserForm_Initialize()
    Dim i As Long
    With Me.ComboBox1
        .Style = fmStyleDropDownList
        For i = 1 To Day(Application.EoMonth(Date, 0))
            .AddItem Format(DateSerial(Year(Date), Month(Date), i), "dd/mm/yyyy")
        Next
    End With
End Sub

You would still need to use CDate function to coerce text to date.

Personally, I always found single Textbox for dates fine so long as users enter something that is recognised as a date & this can be managed with entry error checking - VBA type conversion function like CDATE will change text entry to real date. You then apply required format in range using numberformat.

If you really want to ensure date is entered correctly another way maybe would be to have three textboxes - day month year & then combine the their values to create valid date using CDate function.




Dave