inserting a default date in a textbox in a userform

Tutorials Win: Microsoft Windows Forum

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!
Top

Re: inserting a default date in a textbox in a userform by Jay

Jay
Mon Jan 10 11:55:14 CST 2005

On Mon, 10 Jan 2005 01:33:01 -0800, "HRCarlsberg"
<HRCarlsberg@discussions.microsoft.com> wrote:

>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.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Top

Re: inserting a default date in a textbox in a userform by HRCarlsberg

HRCarlsberg
Mon Jan 10 14:37:07 CST 2005

THANK YOU VERY MUCH! I'm looking forward to try your resolution tomorrow!

"Jay Freedman" skrev:

> On Mon, 10 Jan 2005 01:33:01 -0800, "HRCarlsberg"
> <HRCarlsberg@discussions.microsoft.com> wrote:
>
> >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.
>
> --
> Regards,
> Jay Freedman
> Microsoft Word MVP FAQ: http://word.mvps.org
>
Top