User input macro for a date.

[StargateFanFromWork]

I found the autorun code for a macro to work on Excel 2000
startup, so I have that. But I was hoping to do something
other than a message box. What is needed is for the user
to input a start date into the macro that the macro would
then drop into the appropriate cell (in this case, B7).
The spreadhseet is then geared to dump dates in various
other cells based on that one initial date. Is there a
way to do this, pls?

(The Excel programming group seems to be down, otherwise
I'd ask there. That's an off occurrent, I've never seen
groups down before. <g>)

Thank you. D

 

[JulieD]

Hi StargateFan (long time no see)

you can use an inputbox e.g.
Range("B7") = InputBox("Enter date")

 

[JE McGimpsey]

one way:

Put this in the ThisWorkbook Code module:

Private Sub Workbook_Open()
Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="Enter start date:", _
Title:="Start Date", _
Default:=Format(Date, "dd mmm yyyy"), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until IsDate(vResponse)
Range("B7").Value = CDate(vResponse)
End Sub

 

[StargateFan]

one way:

Put this in the ThisWorkbook Code module:

Private Sub Workbook_Open()
Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="Enter start date:", _
Title:="Start Date", _
Default:=Format(Date, "dd mmm yyyy"), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until IsDate(vResponse)
Range("B7").Value = CDate(vResponse)
End Sub

This works marvellously, thank you! I just changed the macro name to
the autorun one so that that this macro pops up immediately upon
opening the workbook. This is great! The sole purpose of the
spreadsheet is to create a new time sheet for the employees for every
2 week pay period.

Actually, I just thought of something that would make this spreadsheet
perfect. Is there a way to get this user input into one more cell,
say A5? On testing the above situation, I realized that this is so
fantastic that if we could get another cell populated with the start
date, that that would mean that the user needs to only input _one_
items, the initial date called for!

The above start date goes at the top of the column that has each date.
Along the right-hand side people put their hours worked. But just
above B7 where these dates start, there is a box where they must write
in the start and end dates of that pay period. I just thought that if
I split this into 2 cells and have A5 receive the same data input as
B7, then the cell underneath I could easily code to automatically put
in the end date. These are always 2 weeks apart on a Wednesday.

Thanks so much! This is totally awesome. Though I always credit all
these nifty tricks to you all here in the Excel ngs, there's no

 

[JE McGimpsey]

One way would be to change the B7 in the macro to A5, then in B7 enter
the formula

=A5

However if you want both to be entered by macro, add this line

Range("A5").Value = CDate(vResponse)

right after

Range("B7").Value = CDate(vResponse)

FYI: Note that autorun macros, while they still work, have been
deprecated in favor of event macros. The Workbook_Open() event fires
whenever the workbook opens. That event is a workbook-level event, so it
has to live in the Workbook's ThisWorkbook code module rather than a
regular code module.

 

[StargateFanFromWork]

You are _so_ right!! Trouble sometimes is that one wants
to make things more complicated than they are! When I
arrived at the office this morning and had the actual file
to work with I got one of those "d'uh" moments. All I did
was put the other 2 dates (start and end) dependent on
B7. Then I added IF syntax so that if B7 is empty, I
don't get a weird Jan.01.1900 date and the 2 cells remain
blank! <g>

This works like a charm! I know that once we distribute
this, everyone will save loads of time. I already did up
my time sheet for the next pay period and it was so easy!!

Thanks.