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

 

[mopey12345]

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. 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

Thanks for taking the time to reply. Appreciated.

 

[Siddharth Rout]

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

That calendar includes all the validation needed for the date. You have to just copy the files into your project and start using it. After that you just need one line of code to get the date.

There is no hard and fast rule that you cannot use Textboxes or Comboxes for accepting date, but remember, you will have to do lot of validation to ensure that the date entered is correct. If you are ok with writing all that code then why not... You can use them.

One thing that I learned quite early is to never expect your user to behave in a particular way. They will always surprise you.

 

[dmt32]

There is no hard and fast rule that you cannot use Textboxes or Comboxes for accepting date, but remember, you will have to do lot of validation to ensure that the date entered is correct. If you are ok with writing all that code then why not.

Hi,

Should not need massive amounts of code to perform date validation - A simple common Sub could be created to validate date format entry that can be used by any number of textboxes OP has on the userform

For example

Common code in Standard module

Sub CheckValidDate(ByRef FormTextBox As Object)
    Dim ValidDate       As Boolean
    Const DateFormat    As String = "##/##/####"
    
    With FormTextBox
        ValidDate = .Text Like DateFormat And IsDate(.Text)
        .MaxLength = 10
        .BackColor = IIf(Len(.Value) > 0, IIf(ValidDate, rgbLightGreen, rgbRed), rgbWhite)
        .ControlTipText = IIf(ValidDate, "Valid Date Entered", "Enter Date In Format dd/mm/yyyy")
    End With
End Sub

To call it from textbox change event

Private Sub TextBox1_Change()
    CheckValidDate Me.TextBox1
End Sub

Code is just an idea and may need tweaking but should be functional for most uses & could be enhanced say to disable other controls until valid date entered.



Just an idea but hope helpful

Dave

 

[Siddharth Rout]

Hi,

Should not need massive amounts of code to perform date validation - A simple common Sub could be created to validate date format entry that can be used by any number of textboxes OP has on the userform

For example

Common code in Standard module

Sub CheckValidDate(ByRef FormTextBox As Object)
    Dim ValidDate       As Boolean
    Const DateFormat    As String = "##/##/####"
   
    With FormTextBox
        ValidDate = .Text Like DateFormat And IsDate(.Text)
        .MaxLength = 10
        .BackColor = IIf(Len(.Value) > 0, IIf(ValidDate, rgbLightGreen, rgbRed), rgbWhite)
        .ControlTipText = IIf(ValidDate, "Valid Date Entered", "Enter Date In Format dd/mm/yyyy")
    End With
End Sub

To call it from textbox change event

Private Sub TextBox1_Change()
    CheckValidDate Me.TextBox1
End Sub

Code is just an idea and may need tweaking but should be functional for most uses & could be enhanced say to disable other controls until valid date entered.



Just an idea but hope helpful

Dave

IsDate is very forgiving.

Even though you set the format as dd/mm/yyyy Try putting 09/13/2012

 

[dmt32]

format is set ##/##/#### this does not dictate regional format - which could be mm/dd/yyyy or dd/mm/yyyy & any valid entry will be accepted by IsDate.
solution is just an idea & not fool proof but most users would enter the date according to their region style & should suffice in most uses.
If you want to be very strict I would create 3 textboxes (dd mm yyyy) and validate these.

Hope Helpful

Dave

 

[Siddharth Rout]

format is set ##/##/#### this does not dictate regional format - which could be mm/dd/yyyy or dd/mm/yyyy & any valid entry will be accepted by IsDate.
solution is just an idea & not fool proof but most users would enter the date according to their region style & should suffice in most uses.
If you want to be very strict I would create 3 textboxes (dd mm yyyy) and validate these.

Hope Helpful

Dave

I agree with you and hence I mentioned we need lot of validations and hence Textboxes or Comboxes for accepting date is a bad idea.

 

[dmt32]

I agree with you and hence I mentioned we need lot of validations and hence Textboxes or Comboxes for accepting date is a bad idea.

I think we will have to agree to disagree - I have used Textboxes in complex applications with dates for many years without issue. My point here is that validation need not be that complex but its a personal choice.

Keep well

Dave

 

[dmt32]

published for anyone searching, a tweak made to the code which hopefully, will overcome the user keying in date in incorrect regional style.

Sub CheckValidDate(ByRef FormTextBox As Object, Optional ByVal RegionalFormat As String = "dd/mm/yyyy")
    Dim ValidDate       As Boolean
    Const DateFormat    As String = "##/##/####"
    
    With FormTextBox
        ValidDate = .Text Like DateFormat And IsDate(.Text)
        .MaxLength = 10
        If ValidDate Then .Value = Format(DateValue(.Value), RegionalFormat)
        .BackColor = IIf(Len(.Value) > 0, IIf(ValidDate, rgbLightGreen, rgbRed), rgbWhite)
        .ControlTipText = IIf(ValidDate, "Valid Date Entered", "Enter Date In Format dd/mm/yyyy")
    End With
End Sub

Optional RegionalFormat parameter added if need to change to mm/dd/yyyy

Hope Helpful

Dave

 

[mopey12345]

Thanks guys for some great replies.