User Form Date Validation

[Chris Williamson]

Hi Guys,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I’ve got a user form with a few text boxes on it.<o></o>
TextBox4 is for a date to be entered in it.<o></o>
<o></o>
To assist the user to enter the date in the correct format I’ve got in the ‘TextBox4 properties Value field’ the text “dd mmm yyyy”. <o></o>
But some people are still inputting dates in the wrong format.<o></o>
<o></o>
Q) What would the vb macro code be if I wanted the user form to convert the date that is entered into the correct format when they tab to the next text box on the user form?<o></o>
<o></o>
The ideal format that I want is: dd mmm yyyy, <o></o>
So today’s date is: <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:date Year="2012" Day="17" Month="5">18 May 2012</st1:date><o></o>
<o></o>
The incorrect formats that some people input are:<o></o>
<st1:date Year="2012" Day="17" Month="5">18/05/2012</st1:date><o></o>
<st1:date Year="2012" Day="17" Month="5">18/05/12</st1:date><o></o>
<st1:date Year="2012" Day="17" Month="5">18/5/12</st1:date><o></o>
18 may 12<o></o>
18 May 12<o></o>
<o></o>
Thanks for your help.<o></o>
<o></o>
Chris<o></o>

 

[vds1]

Try This,

To Check date is a Valid date

If IsDate(Trim(Textbox4.Text)) = False Then
MsgBox "Please enter the correct date"
End If

'To convert date into dd mmm yyyy format

Textbox4.Text = Format(Textbox4.Text, "dd mmmm yyyy")

 

[Chris Williamson]

This is totally 100% what I am after.

Thank you so much!

 

[Kyle123]

Why not use a microsoft date and time picker control? There's no typing then

 

[karolina1406]

i have a similar problem with my User form. My current code is as below and anytime the user enters a date in a DateRaised_TX textbox, hte date is returned in the US format of mm/dd. Any ideas why?
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Risk_Register")
Dim n As Long
n = sh.Range("I" & Application.Rows.Count).End(xlUp).Row

sh.Range("I" & n + 1).Value = Me.EpicName_CB.Value
sh.Range("j" & n + 1).Value = Me.DateRaised_TX.Value
sh.Range("l" & n + 1).Value = Me.RaisedBy_TX.Value
sh.Range("m" & n + 1).Value = Me.RiskType_CB.Value
sh.Range("n" & n + 1).Value = Me.RiskName_TX.Value
sh.Range("o" & n + 1).Value = Me.RiskDescription_TX.Value
sh.Range("p" & n + 1).Value = Me.ImpactDescription_TX.Value
sh.Range("q" & n + 1).Value = Me.AreaOfImpact_CB.Value
sh.Range("r" & n + 1).Value = Me.RiskValue_TX.Value
sh.Range("t" & n + 1).Value = Me.RiskOWner_TX.Value
sh.Range("u" & n + 1).Value = Me.Impact_CB.Value
sh.Range("v" & n + 1).Value = Me.Probablility_CB.Value
sh.Range("x" & n + 1).Value = Me.MitigationAction_TX.Value
sh.Range("y" & n + 1).Value = Me.PostMitigationValue_TX.Value
sh.Range("aa" & n + 1).Value = Me.ResolveDate_TX.Value


If Me.OptionButton1.Value = True Then
sh.Range("k" & n + 1).Value = "Open"
End If
If Me.OptionButton2.Value = True Then
sh.Range("k" & n + 1).Value = "Closed"
End If
If Me.OptionButton3.Value = True Then
sh.Range("k" & n + 1).Value = "For Escalation"
End If

 

[RoryA]

Use CDate to convert the text to a real date value using your regional settings:

sh.Range("j" & n + 1).Value = CDate(Me.DateRaised_TX.Value)

 

[karolina1406]

Fab! I really appreciate it!

Use CDate to convert the text to a real date value using your regional settings:

sh.Range("j" & n + 1).Value = CDate(Me.DateRaised_TX.Value)