Date formatting in Excel VBA Text Box

[Guest]

Hi all,

I am new to VBA programming so forgive me for an elementary question, but I
can't seem to locate the answer.

I have created a User Form so that a date is entered then that date is
placed in a cell on the Excel spreadsheet. I want to format the Text Box to
only accept a valid date format such ad 01/10/06. How do I do that?

Thanks
TimN

 

[Bob Phillips]

Private Sub TextBox1_Exit()
With TextBox1
If Not IsDate(.Text) Then
MsgBox ("Invalid date")
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End If
End With
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Piotr Lipski]

Dnia Thu, 6 Jul 2006 13:31:01 -0700, TimN napisa³(a):

Hi all,

I am new to VBA programming so forgive me for an elementary question, but I
can't seem to locate the answer.

I have created a User Form so that a date is entered then that date is
placed in a cell on the Excel spreadsheet. I want to format the Text Box to
only accept a valid date format such ad 01/10/06. How do I do that?

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim dtTmp As Date
Cancel = False
On Error GoTo err_baddate
dtTmp = CDate(Me.TextBox1.Text)
Me.TextBox1.Text = dtTmp
On Error GoTo 0
sub_ex:
Exit Sub
err_baddate:
MsgBox "Bad date", vbOKOnly + vbExclamation
Cancel = True
Resume sub_ex
End Sub