Text Box Dates in Userform?

[TotallyConfused]

How do I apply a date "mask" in an Userform textboxes. I have a lot of date
textboxes and want to make sure users do not enter anything else but date in
the textbox. Thank you in advance for any help you can provide.

 

[Dave Peterson]

Excel doesn't support this kind of masking.

Maybe you could use some calendar controls instead?

Ron de Bruin has some notes:
http://www.rondebruin.nl/calendar.htm

 

[joel]

Textboxes are strings and not dates. You must convert the string t
dates and check the date. Use Datevalue to convert from string to Date

the string can be in any valide date format
MyDate = DateValue("February 12, 1969")

You can use ISDate() function to validate that the string is a vali
date.

You mayu also want to add a check to make sure the date is withing
valid range

do
GoodDate = False
MyDate = Texbox1.value
if IsDate(MyDate) then
'check if date is within valid dates
SDate = DateValue(MyDate)
if SDate >= DateValue("1/1/2000") and _
SDate >= DateValue("12/31/2010") then

GoodDate = True
end if
end if
loop while GoodDate = Fals

 

[JP]

You can't, but you can fake it somewhat. Here's one approach:

http://www.codeforexcelandoutlook.com/blog/2009/11/use-textboxes-like-date-or-phone-number-fields/

Technically it's not published yet, but you should still be able to
view it. But you should still follow Joel's advice and use the IsDate
function to verify that whatever is entered into a textbox is a valid
date.

--JP

 

[Mike H]

Hi,

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsDate(TextBox1.Value) Then
MsgBox "Only dates allowed"
TextBox1.Value = ""
Cancel = True
End If
End Sub

Mike

 

[TotallyConfused]

Your right it is not viewable. It says it can't be found. Is there any
other way I can obtain? Thank you.

 

[Jacob Skaria]

Try the below..Textbox1 will display current date....and you can adjust....

Private Sub UserForm_Activate()
Me.TextBox1 = Format(Now, "dd-mmm-yyyy")
End Sub
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
'Date and Time Picker Use down/up arrow to change date/time.
Dim intPos As Integer, strType As String
If KeyCode = 38 Or KeyCode = 40 Then
intPos = Me.TextBox1.SelStart
strType = Application.Lookup(intPos, Array(0, 3, 7), Array("d", "m", "yyyy"))
Me.TextBox1 = Format(DateAdd(strType, (39 - KeyCode), TextBox1),
"dd-mmm-yyyy")
KeyCode = 0
Me.TextBox1.SelStart = intPos
End If
End Sub

If this post helps click Yes

 

[JP]

What you do is define a constant that represents the default date
field value.

Const DEFAULT_DATE_FIELD As String = "__/__/____"

When the form loads, make the textbox look like a date should be
entered:

Private Sub UserForm_Initialize()
' assume textbox name is 'TodaysDate'
Me.TodaysDate.Value = DEFAULT_DATE_FIELD
End Sub

Then use the Enter, Exit and AfterUpdate events to make the end user
believe it's a date field.

Private Sub TodaysDate_Enter()

On Error GoTo ErrorHandler

With Me.TodaysDate
If .Value = DEFAULT_DATE_FIELD Then
.Value = ""
End If
End With

ProgramExit:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub

Private Sub TodaysDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)

On Error GoTo ErrorHandler

With Me.TodaysDate
If Len(.Value) = 0 Then
.Value = DEFAULT_DATE_FIELD
End If
End With

ProgramExit:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub

Private Sub TodaysDate_AfterUpdate()

On Error GoTo ErrorHandler

' format phone number
With Me.TodaysDate
If Len(.Value) = 8 Then
.Value = Format(.Value, "##/##/####")

'.BackColor = RGB(255, 255, 255)

'Application.StatusBar = False

Else
.SetFocus
.SelStart = 0
.SelLength = Len(.Value)

' uncomment this line (and the backcolor line above) if you want
to visually indicate a data entry error
'.BackColor = RGB(255, 0, 0)

' uncomment this line if you want a messagebox indicator,
' although I don't recommend interrupting the user
'MsgBox ("Phone number must be ten digits: ##########")

' uncomment this line (and the statusbar line above) if you want
to use the Status bar to display error msg
'Application.StatusBar = "Phone number must be ten digits:
##########"
End If

End With

ProgramExit:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub