How can I insert a date as a default in a textbox in a userform? It is
important, that the date is shown in a certain format (dd.mm.yyyy). The user
should be able to change the date to another date, but not to anything else
(numbers, text).....
Can anyone please help me? Thank you very much!
Putting today's date into the textbox (assumed to be named tbxDate) is
pretty simple:
Private Sub UserForm_Initialize()
tbxDate.Text = Format(Now, "dd.MM.yyyy")
End Sub
Note that Now is the current system date/time, and the Format function
will make a string from it using the pieces specified in the format
string. The capitalization of the MM is important because lower case
mm is interpreted as the minutes field of Now, not the month.
Restricting the user to entering valid dates is a bit trickier,
particularly because your format with periods as separators isn't
normally recognized by VBA as a valid date format. The idea is to
write an Exit routine for the textbox, which VBA automatically calls
when the focus is about to leave the box:
Private Sub tbxDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If (Not IsDate(tbxDate.Text)) And _
(Not IsDate(Replace(tbxDate.Text, ".", "/"))) Then
MsgBox prompt:="Please enter a valid date", _
Title:="Date Error"
With tbxDate
.SelStart = 0
.SelLength = Len(.Text)
End With
Cancel = True
Else
tbxDate.Text = Format(tbxDate, "dd.MM.yyyy")
End If
End Sub
If the code sets the Cancel parameter to True, the focus doesn't
leave; the cursor stays in the textbox. I used the Replace function to
change any periods in the Text string to slashes so the IsDate
function will recognize your custom format in addition to all the
formats it already knows (e.g., "Jan. 10, 2005").
The .SelStart and .SelLength statements are just a little something
extra: instead of leaving the cursor at the end of the entry in the
textbox, they highlight the entire contents so the user can just start
typing to replace it.
