Validation on a Textbox entry

[thomas.szwed]

Hallo,

I have built a userform for my users to enter some data. The users enter a date in one of the textboxes. I want to know if there is anyway i can put in some validation on that textbox so dates can only be entered in one format (dd/mm/yyyy)???
Also the form is used to change data so a user may open up a record and change the data so the validation on that textbox always needs to be in place whether they are adding or editing a record.

Here is some code i used to validate another textbox - this is to make sure only unique numbers are entered. Perhaps it may be somerthing along the lines of this?

[VBA]
Private Sub txtPersNum_AfterUpdate()

If Not IsError(Application.Match(Val(Me.txtPersNum.Text), Sheets("Master Data").Columns(4), 0)) Then
MsgBox "That Personnel Number is already assigned - try another", vbCritical, "Duplicate found"

With Me

.txtPersNum.Value = vbNullString
.btnExport.Enabled = False
End With
End If
[/VBA]

[rory]

You can't really do it with a textbox - how would you determine whether they meant dd/mm/yyyy rather than mm/dd/yyyy if they entered 01/02/2008? You might use three textboxes or a day textbox, month combo and year textbox?

[Bob Phillips]

I use 3 non-editable textboxes (or labels) with spinners, so that I can control what is entred (can't do 31st Feb for instance). I can post the code if you want.

[thomas.szwed]

I dont really care what they put in, just the format that its put in. so.......

dd/mm/yyyy - so........... 2 numbers forward slash 2 numbers forward slash four numbers

They could be any numbers. But if they put in say "5th December 08", then that would get rejected because its not in the specified format....Do you get my point?

XLD - im not sure if this is what i need but post anyway so i can have a loook

[Bob Phillips]

I don't see your logic, if it is a valid date what does it matter what format they enter it as, you can change it.

My code does not seem what you want.

[thomas.szwed]

Well its a big problem because i have many users who are adding hundreds of records a day. The sheet they are adding it to isnt designed to be edited its just there for records. Its used for reports that extract information from it, and they are all extracting different date formats!

So some come back like 31.12.08
another like 31/12/08
another like 31st December '08

I just want each date to be entered and validated on the userform in this format 31/12/2008

Can u help?

[Bob Phillips]

No, you are not listening. I didn't suggest the sheet was edited, I said reformat valiud dates.

Something like this

[vba]

Private Sub TextBox1_AfterUpdate()
With Me.TextBox1
If Not IsDate(.Text) Then
MsgBox "idiot"
.SelStart = 1
.SelLength = Len(.Text)
.SetFocus
Else
.Text = Format(.Text, "dd/mm/yyyy")
End If
End With
End Sub
[/vba]

[thomas.szwed]

Arrr, i see now. Thanks XLD this is fantastic. I have one more question for you. Sometimes the date isnt know, so in the box the user will write TBC. Can a rule be added onto this statement to allow "TBC" to be entered in the textbox aswell as dates....

Many Thanks

[Bob Phillips]

This should do it

[vba]

Private Sub TextBox1_AfterUpdate()
With Me.TextBox1
If .Text <> "TBC" Then
If Not IsDate(.Text) Then
MsgBox "idiot"
.SelStart = 1
.SelLength = Len(.Text)
.SetFocus
Else
.Text = Format(.Text, "dd/mm/yyyy")
End If
End If
End With
End Sub
[/vba]