I have UserForm3 that has a single box for the user to enter the
date. I would like this box to have the current date in there by
default, and the user can change it if needed.

When the user clicks the OK button, this date will be copied to a range
named Current_Date. I've already designed the form, with a text box,
and the OK button. I know how to get the input to Current_Date once the
user clicks OK. But, I need the default date to show up in the box when
it comes up.

I looked around in the text box properties, and didn't see where I
could set this up. Would code be written in a sub within UserForm3 to
handle this?

Also, I want to make sure the format of the date that would be entered
in the text box can only be mm/dd/yy.

As always, I appreciate any help anyone can offer.

J.O.

Put it in the userform initialize event:

Private Sub UserForm_Initialize()

Text1 = Format(Date, "mm/dd/yy")

End Sub

RBS

"excelnut1954" wrote in message
ups.com...
I have UserForm3 that has a single box for the user to enter the
date. I would like this box to have the current date in there by
default, and the user can change it if needed.

When the user clicks the OK button, this date will be copied to a range
named Current_Date. I've already designed the form, with a text box,
and the OK button. I know how to get the input to Current_Date once the
user clicks OK. But, I need the default date to show up in the box when
it comes up.

I looked around in the text box properties, and didn't see where I
could set this up. Would code be written in a sub within UserForm3 to
handle this?

Also, I want to make sure the format of the date that would be entered
in the text box can only be mm/dd/yy.

As always, I appreciate any help anyone can offer.

J.O.

"excelnut1954" skrev i melding
ups.com...
I have UserForm3 that has a single box for the user to enter the
date. I would like this box to have the current date in there by
default, and the user can change it if needed.

Textbox1.Text = Format(Date, "mm/dd/yy")

I looked around in the text box properties, and didn't see where I
could set this up. Would code be written in a sub within UserForm3 to
handle this?

Yes, in the form's Initialize event, or in the code that shows the form,
like

UserForm3.Textbox1.Text = Format(Date, "mm/dd/yy")
UserForm3.Show

Also, I want to make sure the format of the date that would be entered
in the text box can only be mm/dd/yy.

So you want to throw away perfectly valid date entries because the separator
is not what you planned, or the month is spelled, or the poor soul provided
a four digit year instead of just two ? Don't do it, people will hate your
application and you would too if you were them.

HTH. Best wishes Harald

Thanks for the input. The reason I want the format that way I explained
is because this will be shown in a cell, which will be part of a report
that will print out each day. I want that to look the same each day.
Besides, these people aren't smart enough to know they should hate me.
Thanks again.

If you say so. But if your approach were more like "is it possible to enter
a date in any valid format and have it automatically formatted as mm/dd/yy
in print ?" then your software would be better.

Best wishes Harald

"excelnut1954" skrev i melding
oups.com...
Thanks for the input. The reason I want the format that way I explained
is because this will be shown in a cell, which will be part of a report
that will print out each day. I want that to look the same each day.
Besides, these people aren't smart enough to know they should hate me.
Thanks again.

Ok, I see what you're saying. That makes sense. Thanks for the
followup. I appreaciate your input.

J.O.

Thanks Harald.
Ok. I see what you're saying. I agree. Translate the user input to the
desired format to be printed. That makes much more sense.

So, here's what I'm ultimately trying to do:
The user enters the date, or accepts the default date (which I have set
up already). When the user hits OK, this date is then entered into the
range name Current_Date.
I have that part done, and it works ok. It's adjusting the date format
as it is entered into that range.

Ok... Now, what I need help with is adding the code that will do a
Save-As, and use that date as part of the name. However, I want a
prefix to the date. Example: if the date entered into the range is
12/27/05, then I want the file name to be Staging List 12-27-05.xls

I've previously been helped with doing a Save-As, doing a similar thing
with a different workbook, but with the NOW date from the computer
clock. The difference now is that there are times when this file will
be created after missing a couple days of enteries. So, the actual
current date the file will be save as may not be today. Today might be
the 27th, but they might be working on data from the 23rd. So, even
though it may be the 27th, they might be saving a file Staging List
12-23-05

So, that's why I want the macro to read the range name Current_Date to
get the date part of the new file name that will be saved. Hope that
makes sense.
Here's the code I was given before that only deals with using NOW to
get the date part of the file name.

ActiveWorkbook.SaveAs _
Filename:="Staging List " & Format(Now, "mm-dd-yy") & ".xls", _
FileFormat:=xlWorkbookNormal

I tried playing around with this today, and couldn't find a way to
replace Now with the range name Current_Date. Something in the nature
of
Filename:="Staging List " & Format(Current_Date, "mm-dd-yy") &
".xls", _

I've tried adding quotes, parenthesis, etc. No luck.
Hope you can help.

Thanks,
J.O.
Oh, I entered this problem as a new topic this morning. But, I don't
think this group was working right. I never saw it posted, and the
latest postings were from the 26th. So, if it should ever pop up as a
new topic..... sorry for the redundancy.

You could try something like:

with activeworkbook
.SaveAs _
Filename:="Staging List " _
& Format(.worksheets("whatsheetname").range("Current _date").value, _
"mm-dd-yy") & ".xls", _
FileFormat:=xlWorkbookNormal
end with







excelnut1954 wrote:

Thanks Harald.
Ok. I see what you're saying. I agree. Translate the user input to the
desired format to be printed. That makes much more sense.

So, here's what I'm ultimately trying to do:
The user enters the date, or accepts the default date (which I have set
up already). When the user hits OK, this date is then entered into the
range name Current_Date.
I have that part done, and it works ok. It's adjusting the date format
as it is entered into that range.

Ok... Now, what I need help with is adding the code that will do a
Save-As, and use that date as part of the name. However, I want a
prefix to the date. Example: if the date entered into the range is
12/27/05, then I want the file name to be Staging List 12-27-05.xls

I've previously been helped with doing a Save-As, doing a similar thing
with a different workbook, but with the NOW date from the computer
clock. The difference now is that there are times when this file will
be created after missing a couple days of enteries. So, the actual
current date the file will be save as may not be today. Today might be
the 27th, but they might be working on data from the 23rd. So, even
though it may be the 27th, they might be saving a file Staging List
12-23-05

So, that's why I want the macro to read the range name Current_Date to
get the date part of the new file name that will be saved. Hope that
makes sense.
Here's the code I was given before that only deals with using NOW to
get the date part of the file name.

ActiveWorkbook.SaveAs _
Filename:="Staging List " & Format(Now, "mm-dd-yy") & ".xls", _
FileFormat:=xlWorkbookNormal

I tried playing around with this today, and couldn't find a way to
replace Now with the range name Current_Date. Something in the nature
of
Filename:="Staging List " & Format(Current_Date, "mm-dd-yy") &
".xls", _

I've tried adding quotes, parenthesis, etc. No luck.
Hope you can help.

Thanks,
J.O.
Oh, I entered this problem as a new topic this morning. But, I don't
think this group was working right. I never saw it posted, and the
latest postings were from the 26th. So, if it should ever pop up as a
new topic..... sorry for the redundancy.

--

Dave Peterson

You may want to try a calendar control:

Ron de Bruin has some tips/links at:
http://www.rondebruin.nl/calendar.htm

(or even 3 separate controls to select the month/day/year--just to avoid
ambiguity.)

excelnut1954 wrote:

I have UserForm3 that has a single box for the user to enter the
date. I would like this box to have the current date in there by
default, and the user can change it if needed.

When the user clicks the OK button, this date will be copied to a range
named Current_Date. I've already designed the form, with a text box,
and the OK button. I know how to get the input to Current_Date once the
user clicks OK. But, I need the default date to show up in the box when
it comes up.

I looked around in the text box properties, and didn't see where I
could set this up. Would code be written in a sub within UserForm3 to
handle this?

Also, I want to make sure the format of the date that would be entered
in the text box can only be mm/dd/yy.

As always, I appreciate any help anyone can offer.

J.O.

--

Dave Peterson