C
Claus Mygind
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?
entries ie: mm/dd/yy or mm/dd/yyyy?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Bob Phillips
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.
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.
D
Dave Peterson
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.
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.
C
Claus Mygind
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
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
D
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?
You will have to move Bob's code to a different procedure, though, right?
Maybe the "TextBox1_Exit" procedure?
P
ph8
Dave said: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?
C
Claus Mygind
Yes that would be correct. Thanks for the tip!
Dave Peterson said: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?