Selecting Date

[HomeTaught]

HI
I have a spreadsheet that I want to have a field default to todays date but
if selected I want it to display a selection of dates from 10 days before
todays date to 5 days after todays date. If possible I would like to be able
to add a date outside of this range if necessary. Is this possible? Any help
would be greatly appreciated. Thanks

 

[Gord Dibben]

You could use Data Validation dropdown to select the date.

On a separate worksheet in A11 enter =TODAY()

In A10 enter =A11 - 1 copy up to A1

In A12 enter =A11 + 1 copy down to A16

Name the range A1:A16 as mydates

On sheet1 select a cell and Data>Validation>List

In Source enter =mydates

Uncheck error alert so's you can enter a date off the list.

It will only default to Today's date if you use sheet event code like

Private Sub Worksheet_Activate()
Me.Range("D1").Value = Date
End Sub

OR..............

Private Sub Workbook_Open()
Sheets("Sheet1").Range("D1").Value = Date
End Sub

Assumes D1 is the DV dropdown cell.


Gord Dibben MS Excel MVP

 

[HomeTaught]

HI
I have a spreadsheet that I want to have a field default to todays date but
if selected I want it to display a selection of dates from 10 days before
todays date to 5 days after todays date. If possible I would like to be able
to add a date outside of this range if necessary. Is this possible? Any help
would be greatly appreciated. Thanks

Thanks SO MUCH GORD that was so easy to how I have been trying to do it for
the past week ... BUT ... (there is always a but) .. I can't get the code to
work that sets up the default date .. I have copied and pasted and changed
the sells and sheet name to match mine but I think I put it in the wrong
place .. I just opened Developer/visual basic and then pasted in there .. is
that correct?

Thanks

 

[Gord Dibben]

Depends which code you chose to use.

I posted two types of events, neither of which belongs in a standard module.

One is worksheet event code that runs when the sheet is activated.

That code would go into the appropriate sheet module.

Right-click on the sheet tab and "View Code" or if in VBE just double-click
on the sheet module name.

Paste into that sheet module.

The other code is workbook_open code which will set the cell to Today's date
when the workbook is opened.

In VBE, select your workbook/project and expand it.

Double-click on Thisworkbook module.

Paste the workbook_open code into that module.

Nothing wrong with having both sets of code but could be overkill.

I guess it depends upon when you want the date value to default to today's
date.


Gord

 

[HomeTaught]

THANKS SO MUCH again Gord .. I had chosen the workbook_open code but had
obviously put it in the wrong spot. I have now moved it to where you said and
it works like a dream .. Thanks again and have a great rest of the day

Alison

 

[HomeTaught]

Hi Gord,
One more thing .. when I reopen the sheet (after saving it with a date) I do
not want it to alter the saved date to todays date. Ie. I only want it to put
a date in the first time the template is opened and then when it is saved as
an XLS sheet I want it to allow me to change the date but not automatically
put in todays date. The original template is save with no date but I cannot
get my if statement to work (I tried to get something like " if cell is blank
put in todays date else leave cell with existing date" working?

Thanks again

 

[Gord Dibben]

Easy enough to add code to "if cell is blank put in today's date else leave
existing date".

Private Sub Workbook_Open()
With Sheets("Sheet1").Range("D1")
If .Value = "" Then
.Value = Date
End If
End With
End Sub


Gord

 

[HomeTaught]

Thanks Again Gord that works a treat.

Easy enough to add code to "if cell is blank put in today's date else leave
existing date".

Private Sub Workbook_Open()
With Sheets("Sheet1").Range("D1")
If .Value = "" Then
.Value = Date
End If
End With
End Sub


Gord



.