Force Date Format In TextBox

pangolin · Jan 5th 2005

Hi everybody

Obviously there are hundreds of threads on the above subject but really i have not been able to find the correct answer and I myself have not been able to figure out the same...so thats why I am asking all you excel gurus to pl help me out

On a user form i have a textbox for accepting date. Earlier I had linked it to calendar but now the user does not want calendar but a direct input in the textbox whereupon clicking a commandbutton the date will get supplied to another code.

My problem is this

1...How do I make the textbox accept date only in the "dd/mm/yyyy" format
2....ensure that error message is displayed if there is departure from format (even though the date may otherwise be valid)
3....supply the date in "dd/mm/yyyy" format to the subsequent code

Thanx in advance

pangolin

**iwrk4dedpr** · Jan 5th 2005

Re: Date format in text

pangolin,

First off letting the user type in the date string can be problematic. Hence, I believe you're better off using a date control. However, users being users, want want want. So if they want to type in the date on the exit event of the text box use the function ISDATE.

It'll be on them to enter the MM/DD/YY correctly.

Ranger · Jan 5th 2005

Re: Date format in text

Hi Pangolin,

The following code will allow the user to put in different formats, but change after input to dd/mm/yyyy. Clicking the CommandButton will put the value of the date TextBox into Cell G1 of Sheet1 in the format dd/mm/yyyy.

Code

Dim dDate As Date
Private Sub CommandButton1_Click()
    Sheet1.Range("G1").Value = dDate
End Sub


Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    
    'Dim dDate As Date
    dDate = DateSerial(Year(Date), Month(Date), Day(Date))
    TextBox1.Value = Format(TextBox1.Value, "dd/mm/yyyy")
    dDate = TextBox1.Value
End Sub

Display More

Hope this helps.

Regards,

Bill

pangolin · Jan 5th 2005

Re: Date format in text

Hi ranger

Thanx for ur help...the code works but if I put a date valid in mm/dd but invalid in dd/mm (for eg 18/12) it still accepts the date..in such a case I would like it to give an error message and disallow acceptance

secondly after acceptance the value in the textbox reverts back to "mm/dd" format...can it stay in the "dd/mm" format

thanx once again

pangolin

Ranger · Jan 5th 2005

Re: Date format in text

Hi Pangolin,

Can you check in the Control Panel, that your date is not set to U.S. Format, as you seem to want European Format in your first post. The date should return to dd/mm/yyyy in the TextBox.

I will have a look at the invalid dates problem.

Regards,

Bill

pangolin · Jan 5th 2005

Re: Date format in text

Hi Ranger

Yeah you very right the date was in the US format (sorry bout that- didnt check over there in the first place)

However the problem regarding invalid date acceptance still remains...even I am working on it separately but yet unable to figure it out.....

Anyways...thanx a million for all your help...without you even this much wouldnt have been possible

pangolin

pangolin · Jan 6th 2005

Re: Date format in text

Hi Ranger

any luck???????

pangolin

pangolin · Jan 7th 2005

Re: Date format in text

Hi everybody

well surprisingly I am not able to find a solution to my problem...

can someone help me on this

TIA

pangolin

Ranger · Jan 7th 2005

Re: Date format in text

Hi Pangolin,

Try the following:

Code

Dim dDate As Date
Private Sub CommandButton1_Click()
    Sheet1.Range("G1").Value = dDate
End Sub
 
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Mid(TextBox1.Value, 4, 2) > 12 Then
        MsgBox "Invalid date, please re-enter", vbCritical
        TextBox1.Value = vbNullString
        TextBox1.SetFocus
        Exit Sub
    End If
     
    dDate = DateSerial(Year(Date), Month(Date), Day(Date))
    TextBox1.Value = Format(TextBox1.Value, "dd/mm/yyyy")
    dDate = TextBox1.Value
End Sub

Display More

Regards,

Bill

pangolin · Jan 10th 2005

Re: Date format in text

Thanx Ranger

this works perfectly

pangolin

Force Date Format In TextBox

Participate now!

Share