Text Box to Date format on Userform 1

[WelshyWizard]

Hi all,

Just a quick one.

I've got a textbox on a userform in Excel 2003.
I want the user to be limited in what they can enter into the field. They should only be able to enter dates in the textbox with the following format 28/12/1976.

Any ideas guys and girls?

Cheers

Today is the tomorrow you worried about yesterday - and all is well.....

 

[CautionMP]

WelshyWizard,
A couple of thoughts. You could use [tt]LIKE[/tt] to test the value in the box ([tt]textbox[/tt]) in a validation routine.

If Me.TextBox1.Value Like "##/##/####" Then
  If Not IsDate(Me.TextBox1.Value) Then
    MsgBox "Invalid input", vbCritical, "Input Error"
  End If
Else
  MsgBox "Invalid input", vbCritical, "Input Error"
End If

If you want to go a step farther you can use the [tt]KeyDown[/tt] event to limit what characters can be entered in the textbox.

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 48, 49, 50, 51, 52, 53, 54, 55, 56, 57
  'A number was pressed
Case 191
  'Slash
Case 8
  'Backspace was pressed
Case Else
  Debug.Print KeyCode
  KeyCode = 0
End Select
End Sub

If you go this route you will need to check if the [tt]KeyCode[/tt]'s for the ten key on your keyboard are different and add them as a [tt]Case[/tt].

Hope this helps,
CMP



[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[taupirho]

Hi checkout my article below on bullet-proofing the input of dates on vba userforms

http://the-big-o.port5.com/article41.shtml

 

[Rofeu]

Hello,

why not use a DatePicker instead of a textbox?

Cheers,

Roel

 

[Rofeu]

To get the datepicker:

right click in the Toolbox, click 'Additional Controls' and select 'Microsoft Date and Time Picker Control 6.0 (SP4)'


In the properties go to 'Custom' then select '3 - dtpCustom' for Format and put your format-string in 'CustomFormat'

Cheers,

Roel