Default date in a UserForm box

[excelnut1954]

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.

 

[RB Smissaert]

Put it in the userform initialize event:

Private Sub UserForm_Initialize()

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

End Sub

RBS

 

[Harald Staff]

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

 

[excelnut1954]

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.

 

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

 

[Harald Staff]

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]

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

J.O.

 

[excelnut1954]

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

Thanks Dave

Actually, I came up with that very same line. And, it works! I looked
at various previous postings here about similar problems, and with some
dumb luck, I stumbled onto it.

I really do appreciate your response. I've gotten alot of help at this
site from alot of folks. I try to figure out a problem on my own. After
I hit a wall, I lay it out here, and hope someone can help. But, I
still work on it (playing with the coding, and researching older posts)
while waiting for a response.

Thanks again for your help.
J.O.

 

[Dave Peterson]

Glad you got it working.

Google is a very good friend, huh? <vbg>

 

[excelnut1954]

Yes, this Google site is excellent!

I have a twist in this project I just discovered this afternoon.
This file resides on a server drive. When I ran the macro including the
Save-as code above, it worked fine. However, I found out I need to
specify the path.

When the person who does the entries each day ran it, the file saved on
his hard drive. It seems that it went to the last place he pulled a
file from, even though it was a different file, which was from his hard
drive. This baffled me, since anytime I've ever run a Save-as from any
program, it always assumes the directory it came from.

Well, I guess I should learn good form, and put it in anyway.

So, I figured out how to show the path as far as the directories go.
But, I'll probably need to show the drive letter also. The problem is
that on different computers in our company, the drive letter can be
different to the same server drive. On my pc, this drive letter is F
On another person's, it's H, and who knows what other letters are being
used to access this same server.

Using the relevant part of the code above, here is what I have so far:
Filename:="\Material Staging List\Staging List " & ........
The server drive is called Public.
So, I assume it would look like
Filename:="\Public\Material Staging List\Staging List " & ........
Will I need to identify the drive letter? I would think so.

In the long run, I want different people to be able to update this file
using the macros I'm designing. How do I get around the drive letter
differentiation from pc to pc?

As always, my humble appreciation to all who take the time to respond.
J.O.

 

[Dave Peterson]

You can use the UNC path (\\server\sharename\folder\folder) instead of the
mapped drive letter:

Dim myFolder as string

myfolder = "\\server\sharename\folder\folder"
if right(myfolder,1) <> "\" then
myfolder = myfolder & "\"
end if

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

 

[Tom Ogilvy]

If the activeworkbook is already located in the correct folder then

Filename:=ActiveWorkbook.Path & "\Staging List " _
& Format(.worksheets("whatsheetname") _
.range("Current_date").value, _
"mm-dd-yy") & ".xls", _
FileFormat:=xlWorkbookNormal

 

[excelnut1954]

Thanks Tom

This is the part I needed.
Filename:=ActiveWorkbook.Path & "\Staging List " _ .............

This works fine.

I see where there are other replies that refer to the "UNC path". I was
reading about that in other posts dealing with this type of coding. I
don't understand it yet. But, I'll get into that subject area later.
For now, this was at the very least, a quick fix.
I also posted a new topic about this. Sorry for the confusion. After
posting this here, I thought that maybe I should have just started a
new topic, that this might get buried.... and it was actually a
different problem than my original post here.
Anyway, still learning how this site operates. All I know is that there
is no shortage of help here.
Thanks to all who chime in.

J.O.