Formatting a text box for date entry only

Is there a way to format a text box on a user form to only allow date
entries ie: mm/dd/yy or mm/dd/yyyy?

Not really, but you can ensure it is a date, like so

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim fValid As Boolean

fValid = False
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
If IsNumeric(.Value) Then
If IsDate(.Value) Then
If Year(.Value) >= 1970 And Year(.Value) <= 2999 Then
fValid = True
End If
End If
End If
If Not fValid Then
MsgBox "Invalid value"
.Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


You can tune the limits.

--
HTH

Bob Phillips

"Claus Mygind" <[email protected]> wrote in message
news:%[email protected]...
> Is there a way to format a text box on a user form to only allow date
> entries ie: mm/dd/yy or mm/dd/yyyy?
>
>

The subject says a textbox--but it doesn't give a hint about where that textbox
is--on a worksheet or on a userform.

And if it's on a worksheet, it doesn't say if it's a textbox from the drawing
toolbar or from the Control toolbox toolbar.

==
To the OP, you may want to look at using a calendar control.

There's a link to a free calendar control at the bottom of Ron de Bruin's page:
http://www.rondebruin.nl/calendar.htm

Ron has some instructions on how to use it on that same page.



Bob Phillips wrote:
>
> Not really, but you can ensure it is a date, like so
>
> Option Explicit
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim fValid As Boolean
>
> fValid = False
> On Error GoTo ws_exit
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
> With Target
> If IsNumeric(.Value) Then
> If IsDate(.Value) Then
> If Year(.Value) >= 1970 And Year(.Value) <= 2999 Then
> fValid = True
> End If
> End If
> End If
> If Not fValid Then
> MsgBox "Invalid value"
> .Value = ""
> End If
> End With
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
> You can tune the limits.
>
> --
> HTH
>
> Bob Phillips
>
> "Claus Mygind" <[email protected]> wrote in message
> news:%[email protected]...
> > Is there a way to format a text box on a user form to only allow date
> > entries ie: mm/dd/yy or mm/dd/yyyy?
> >
> >

--

Dave Peterson

Dave
Thanks for the input. I did specify that the text box was on a user form.
But I like the information with the calander picker. I am going to see if I
can work that in. Thanks for the response. For a quick fix I may use Bob's
example as I see I can plug that in fairly easily.

Claus


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> The subject says a textbox--but it doesn't give a hint about where that
textbox
> is--on a worksheet or on a userform.
>
> And if it's on a worksheet, it doesn't say if it's a textbox from the
drawing
> toolbar or from the Control toolbox toolbar.
>
> ==
> To the OP, you may want to look at using a calendar control.
>
> There's a link to a free calendar control at the bottom of Ron de Bruin's
page:
> http://www.rondebruin.nl/calendar.htm
>
> Ron has some instructions on how to use it on that same page.
>
>
>
> Bob Phillips wrote:
> >
> > Not really, but you can ensure it is a date, like so
> >
> > Option Explicit
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim fValid As Boolean
> >
> > fValid = False
> > On Error GoTo ws_exit
> > Application.EnableEvents = False
> > If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
> > With Target
> > If IsNumeric(.Value) Then
> > If IsDate(.Value) Then
> > If Year(.Value) >= 1970 And Year(.Value) <= 2999
Then
> > fValid = True
> > End If
> > End If
> > End If
> > If Not fValid Then
> > MsgBox "Invalid value"
> > .Value = ""
> > End If
> > End With
> > End If
> >
> > ws_exit:
> > Application.EnableEvents = True
> > End Sub
> >
> > 'This is worksheet event code, which means that it needs to be
> > 'placed in the appropriate worksheet code module, not a standard
> > 'code module. To do this, right-click on the sheet tab, select
> > 'the View Code option from the menu, and paste the code in.
> >
> > You can tune the limits.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Claus Mygind" <[email protected]> wrote in message
> > news:%[email protected]...
> > > Is there a way to format a text box on a user form to only allow date
> > > entries ie: mm/dd/yy or mm/dd/yyyy?
> > >
> > >
>
> --
>
> Dave Peterson

Yes, you did have that userform note. I missed it.

You will have to move Bob's code to a different procedure, though, right?

Maybe the "TextBox1_Exit" procedure?



Claus Mygind wrote:
>
> Dave
> Thanks for the input. I did specify that the text box was on a user form.
> But I like the information with the calander picker. I am going to see if I
> can work that in. Thanks for the response. For a quick fix I may use Bob's
> example as I see I can plug that in fairly easily.
>
> Claus
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > The subject says a textbox--but it doesn't give a hint about where that
> textbox
> > is--on a worksheet or on a userform.
> >
> > And if it's on a worksheet, it doesn't say if it's a textbox from the
> drawing
> > toolbar or from the Control toolbox toolbar.
> >
> > ==
> > To the OP, you may want to look at using a calendar control.
> >
> > There's a link to a free calendar control at the bottom of Ron de Bruin's
> page:
> > http://www.rondebruin.nl/calendar.htm
> >
> > Ron has some instructions on how to use it on that same page.
> >
> >
> >
> > Bob Phillips wrote:
> > >
> > > Not really, but you can ensure it is a date, like so
> > >
> > > Option Explicit
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Dim fValid As Boolean
> > >
> > > fValid = False
> > > On Error GoTo ws_exit
> > > Application.EnableEvents = False
> > > If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
> > > With Target
> > > If IsNumeric(.Value) Then
> > > If IsDate(.Value) Then
> > > If Year(.Value) >= 1970 And Year(.Value) <= 2999
> Then
> > > fValid = True
> > > End If
> > > End If
> > > End If
> > > If Not fValid Then
> > > MsgBox "Invalid value"
> > > .Value = ""
> > > End If
> > > End With
> > > End If
> > >
> > > ws_exit:
> > > Application.EnableEvents = True
> > > End Sub
> > >
> > > 'This is worksheet event code, which means that it needs to be
> > > 'placed in the appropriate worksheet code module, not a standard
> > > 'code module. To do this, right-click on the sheet tab, select
> > > 'the View Code option from the menu, and paste the code in.
> > >
> > > You can tune the limits.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Claus Mygind" <[email protected]> wrote in message
> > > news:%[email protected]...
> > > > Is there a way to format a text box on a user form to only allow date
> > > > entries ie: mm/dd/yy or mm/dd/yyyy?
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Originally Posted by Dave Peterson

There's a link to a free calendar control at the bottom of Ron de Bruin's page:
http://www.rondebruin.nl/calendar.htm

Ron has some instructions on how to use it on that same page.

I am sorry that this is a little off topic. But does anyone have something similar but that also incorporates time?

Yes that would be correct. Thanks for the tip!

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Yes, you did have that userform note. I missed it.
>
> You will have to move Bob's code to a different procedure, though, right?
>
> Maybe the "TextBox1_Exit" procedure?
>
>
>
> Claus Mygind wrote:
> >
> > Dave
> > Thanks for the input. I did specify that the text box was on a user
form.
> > But I like the information with the calander picker. I am going to see
if I
> > can work that in. Thanks for the response. For a quick fix I may use
Bob's
> > example as I see I can plug that in fairly easily.
> >
> > Claus
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> > > The subject says a textbox--but it doesn't give a hint about where
that
> > textbox
> > > is--on a worksheet or on a userform.
> > >
> > > And if it's on a worksheet, it doesn't say if it's a textbox from the
> > drawing
> > > toolbar or from the Control toolbox toolbar.
> > >
> > > ==
> > > To the OP, you may want to look at using a calendar control.
> > >
> > > There's a link to a free calendar control at the bottom of Ron de
Bruin's
> > page:
> > > http://www.rondebruin.nl/calendar.htm
> > >
> > > Ron has some instructions on how to use it on that same page.
> > >
> > >
> > >
> > > Bob Phillips wrote:
> > > >
> > > > Not really, but you can ensure it is a date, like so
> > > >
> > > > Option Explicit
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > Dim fValid As Boolean
> > > >
> > > > fValid = False
> > > > On Error GoTo ws_exit
> > > > Application.EnableEvents = False
> > > > If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
> > > > With Target
> > > > If IsNumeric(.Value) Then
> > > > If IsDate(.Value) Then
> > > > If Year(.Value) >= 1970 And Year(.Value) <= 2999
> > Then
> > > > fValid = True
> > > > End If
> > > > End If
> > > > End If
> > > > If Not fValid Then
> > > > MsgBox "Invalid value"
> > > > .Value = ""
> > > > End If
> > > > End With
> > > > End If
> > > >
> > > > ws_exit:
> > > > Application.EnableEvents = True
> > > > End Sub
> > > >
> > > > 'This is worksheet event code, which means that it needs to be
> > > > 'placed in the appropriate worksheet code module, not a standard
> > > > 'code module. To do this, right-click on the sheet tab, select
> > > > 'the View Code option from the menu, and paste the code in.
> > > >
> > > > You can tune the limits.
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "Claus Mygind" <[email protected]> wrote in message
> > > > news:%[email protected]...
> > > > > Is there a way to format a text box on a user form to only allow
date
> > > > > entries ie: mm/dd/yy or mm/dd/yyyy?
> > > > >
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson