Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Date
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Date
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 DataValidationList 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 On Mon, 4 Jan 2010 14:06:01 -0800, HomeTaught wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Date
"HomeTaught" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Date
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 On Mon, 4 Jan 2010 16:11:01 -0800, HomeTaught wrote: "HomeTaught" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Date
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 "Gord Dibben" wrote: 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 On Mon, 4 Jan 2010 16:11:01 -0800, HomeTaught wrote: "HomeTaught" wrote: 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 . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Date
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 "HomeTaught" wrote: 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 "Gord Dibben" wrote: 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 On Mon, 4 Jan 2010 16:11:01 -0800, HomeTaught wrote: "HomeTaught" wrote: 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 . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Date
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 On Thu, 7 Jan 2010 15:20:02 -0800, HomeTaught wrote: 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 "HomeTaught" wrote: 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 "Gord Dibben" wrote: 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 On Mon, 4 Jan 2010 16:11:01 -0800, HomeTaught wrote: "HomeTaught" wrote: 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 . |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Date
Thanks Again Gord that works a treat.
"Gord Dibben" wrote: 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 On Thu, 7 Jan 2010 15:20:02 -0800, HomeTaught wrote: 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 "HomeTaught" wrote: 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 "Gord Dibben" wrote: 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 On Mon, 4 Jan 2010 16:11:01 -0800, HomeTaught wrote: "HomeTaught" wrote: 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting data by date differences | Excel Worksheet Functions | |||
Selecting date range for counts | Excel Worksheet Functions | |||
selecting the first date in a range of dates | Excel Worksheet Functions | |||
Selecting Current date | Excel Discussion (Misc queries) | |||
Selecting data within a date range | Excel Worksheet Functions |