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

 

[p45cal]

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

Put this formula in a cell somewhere out of the way (I used A9)
preferably/possibly on another (hidden?) sheet:
=TODAY()-5
put this formula immediately below it:
=A9+1
you should substitute A9 with the cell address of the first formula.
Copy the second cell down 14 or 15 cells.
Name the whole range "DateRange".
In Data Validation choose List: in the Allow: field then put
=DateRange
in the Source: field.
Under the Error Alert tab, remove the tick from the 'Show error aler
after invalid data is entered'

 

[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



.