|
If cell is blank automatically enter today's date in the same cell
Can someone please help with the following? I have a blank cell in say M10 which when a date is typed into it is used in another formula which counts all those entries before that date which are overdue. What I would like to happen is if the cell is blank then todays date is automatically entered. I have tried the following formula but all I get is the word FALSE and I need todays Date. Can someone out there please assist?
=IF(ISBLANK(""),TODAY()) Thanks |
Answer: If cell is blank automatically enter today's date in the same cell
Excel Formula for Entering Today's Date if Cell is Blank
Here's the corrected formula: Formula:
To use this formula, simply copy and paste it into the cell where you want the date to appear (in this case, M10). Once you've done that, the cell will automatically update with today's date if it's left blank.
I hope that helps! |
If cell is blank automatically enter today's date in the same cell
LCTECH001 explained :
Can someone please help with the following? I have a blank cell in say M10 which when a date is typed into it is used in another formula which counts all those entries before that date which are overdue. What I would like to happen is if the cell is blank then todays date is automatically entered. I have tried the following formula but all I get is the word FALSE and I need todays Date. Can someone out there please assist? =IF(ISBLANK(""),TODAY()) Thanks You can't use a formula in M10 because it would be a circular reference (refers to itself). What you need is a macro to enter today's date, something like... In a standard code module: Option Explicit Sub SetTodaysDate() With Range("M10") If .Value = "" Then .Value = Date End With End Sub ...which you could call from the Worksheet_Activate event... In the code module behind the worksheet: Option Explicit Private Sub Worksheet_Activate() Call SetTodaysDate End Sub --OR-- ...simply use the Worksheet_Activate event... Private Sub Worksheet_Activate() With Range("M10") If .Value = "" Then .Value = Date End With End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
If cell is blank automatically enter today's date in the same cell
On Jan 11, 2:03*pm, GS wrote:
LCTECH001 explained : Can someone please help with the following? I have a blank cell in say M10 which when a date is typed into it is used in another formula which counts all those entries before that date which are overdue. What I would like to happen is if the cell is blank then todays date is automatically entered. I have tried the following formula but all I get is the word FALSE and I need todays Date. Can someone out there please assist? =IF(ISBLANK(""),TODAY()) Thanks You can't use a formula in M10 because it would be a circular reference (refers to itself). What you need is a macro to enter today's date, something like... In a standard code module: * Option Explicit * Sub SetTodaysDate() * * With Range("M10") * * * If .Value = "" Then .Value = Date * * End With * End Sub ..which you could call from the Worksheet_Activate event... In the code module behind the worksheet: * Option Explicit * Private Sub Worksheet_Activate() * * Call SetTodaysDate * End Sub --OR-- ..simply use the Worksheet_Activate event... * Private Sub Worksheet_Activate() * * With Range("M10") * * * If .Value = "" Then .Value = Date * * End With * End Sub -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hi Garry, Thank you for your quick response. I am unfortunately a bit of a novice so just getting my head around excel formulas but I would really like to give this a go! So far I've right clicked on the worksheet and have copied your code in the code module behind the worksheet but I'm now stumped what to do next?? I have been checking out the internet on how to do this and its talking about how I have to change security settings (i'm using Excel 2007) to medium etc etc which is a whole new world to me and I would like to know more before I get myself into more problems!!! Can you advise or is this going to be a nightmare for you to explain! Thanks in advance L |
If cell is blank automatically enter today's date in the same cell
The code will run whenever the sheet is activated. If the workbook is
saved with that sheet the active sheet then the code will run when the file opens. Not, though, tht it will also run if you switch back to that sheet after working on another sheet, OR working in another workbook and switching back to that sheet. Once you put macros into a workbook, you'll need to change Excel's Macro Security to use the macros whenever the workbook is reopened. Everyone that uses this file will have to do the same. In Excel2007... Home button Excel Options Trust Center Trust Center Settings... In the Trust Center dialog... Macro Settings ..choose option2 under 'Macro Settings' section ...which will disable macros with notification that prompts you to enable them via a warning bar at the top of the worksheet. Note that leaving macros disabled will require making the changes manually, same as without the macro. HTH -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
If cell is blank automatically enter today's date in the same cell
GS has a motor skills disability that causes him to miss keystrokes
when he's tired. Geez, I hate when that happens! The code will run whenever the sheet is activated. If the workbook is saved with that sheet the active sheet then the code will run when the file opens. Note, though, that it will also run if you switch back to that sheet after working on another sheet, OR working in another workbook and switching back to that sheet. Once you put macros into a workbook, you'll need to change Excel's Macro Security to use the macros whenever the workbook is reopened. Everyone that uses this file will have to do the same. In Excel2007... Home button Excel Options Trust Center Trust Center Settings... In the Trust Center dialog... Macro Settings ..choose option2 under 'Macro Settings' section ..which will disable macros with notification that prompts you to enable them via a warning bar at the top of the worksheet. Note that leaving macros disabled will require making the changes manually, same as without the macro. HTH -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
If cell is blank automatically enter today's date in the same cell
You should re-think this statement GS
If the workbook is saved with that sheet the active sheet then the code will run when the file opens Code will run only when the sheet is activated. Opening a workbook does not activate a sheet. Gord |
If cell is blank automatically enter today's date in the same cell
Gord Dibben brought next idea :
You should re-think this statement GS If the workbook is saved with that sheet the active sheet then the code will run when the file opens Code will run only when the sheet is activated. Opening a workbook does not activate a sheet. Gord Yes, you're absolutely right! Hmm.., and so I will rethink this... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
If cell is blank automatically enter today's date in the same cell
Scrap all the previous code posted, and delete it ALL from your
workbook! In the VBE, double-click ThisWorkbook and paste the following into the code window: Option Explicit Private Sub Workbook_Open() With Sheets("<sheetname").Range("M10") If .Value = "" Then .Value = Date End With End Sub **Be sure to substitute <sheetname with the actual sheet name! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
If cell is blank automatically enter today's date in the same cell
On Jan 12, 12:29*pm, GS wrote:
Scrap all the previous code posted, and delete it ALL from your workbook! In the VBE, double-click ThisWorkbook and paste the following into the code window: Option Explicit Private Sub Workbook_Open() * With Sheets("<sheetname").Range("M10") * *If.Value = "" Then .Value =Date * End With End Sub **Be sure to substitute *<sheetname with the actual sheet name! -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hi Garry, Thank you for assisting with this! I have etered this new code into the VBE Code sheet but nothing is happening! When I copy and paste the code in I have pressed the save icon but I'm not sure if I have to save the code as a macro? Do I name it to run it in the actual worksheet as I have deleted the formula I had in M10 and now the cell is blank and todays date is not appearing! Can you advise what I'm doing wrong? Best Regards LC |
If cell is blank automatically enter today's date in the same cell
LCTECH001 laid this down on his screen :
On Jan 12, 12:29*pm, GS wrote: Scrap all the previous code posted, and delete it ALL from your workbook! In the VBE, double-click ThisWorkbook and paste the following into the code window: Option Explicit Private Sub Workbook_Open() * With Sheets("<sheetname").Range("M10") * *If.Value = "" Then .Value =Date * End With End Sub **Be sure to substitute *<sheetname with the actual sheet name! -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hi Garry, Thank you for assisting with this! I have etered this new code into the VBE Code sheet but nothing is happening! When I copy and paste the code in I have pressed the save icon but I'm not sure if I have to save the code as a macro? Do I name it to run it in the actual worksheet as I have deleted the formula I had in M10 and now the cell is blank and todays date is not appearing! Can you advise what I'm doing wrong? Best Regards LC The code goes in the code window behind ThisWorkbook. It will run every time the workbook is opened. To access the VBE keyboard Ctrl+F11 and then expand the 'Microsoft Excel Objects' folder. Double-click on 'ThisWorkbook' and paste the code. ***Make sure you edit <sheetname to match the name of the sheet you want this to affect*** **Remove this code if you put it anywhere other than in ThisWorkbook** -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
If cell is blank automatically enter today's date in the same cell
Hi Garry
I think you meant to say To access the VB editor press Alt+F11 not Ctrl+F11 Best regards Cimjet "GS" wrote in message ... LCTECH001 laid this down on his screen : On Jan 12, 12:29 pm, GS wrote: Scrap all the previous code posted, and delete it ALL from your workbook! In the VBE, double-click ThisWorkbook and paste the following into the code window: Option Explicit Private Sub Workbook_Open() With Sheets("<sheetname").Range("M10") If.Value = "" Then .Value =Date End With End Sub **Be sure to substitute <sheetname with the actual sheet name! -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hi Garry, Thank you for assisting with this! I have etered this new code into the VBE Code sheet but nothing is happening! When I copy and paste the code in I have pressed the save icon but I'm not sure if I have to save the code as a macro? Do I name it to run it in the actual worksheet as I have deleted the formula I had in M10 and now the cell is blank and todays date is not appearing! Can you advise what I'm doing wrong? Best Regards LC The code goes in the code window behind ThisWorkbook. It will run every time the workbook is opened. To access the VBE keyboard Ctrl+F11 and then expand the 'Microsoft Excel Objects' folder. Double-click on 'ThisWorkbook' and paste the code. ***Make sure you edit <sheetname to match the name of the sheet you want this to affect*** **Remove this code if you put it anywhere other than in ThisWorkbook** -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
If cell is blank automatically enter today's date in the same cell
Cimjet wrote on 1/17/2012 :
Hi Garry I think you meant to say To access the VB editor press Alt+F11 not Ctrl+F11 Best regards Cimjet Yes, thanks! Nice catch... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
If cell is blank automatically enter today's date in the same cell
correction...
The code goes in the code window behind ThisWorkbook. It will run every time the workbook is opened. To access the VBE keyboard Alt+F11 and then expand the 'Microsoft Excel Objects' folder. Double-click on 'ThisWorkbook' and paste the code. ***Make sure you edit <sheetname to match the name of the sheet you want this to affect*** **Remove this code if you put it anywhere other than in ThisWorkbook** -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
If cell is blank automatically enter today's date in the same cell
On Jan 18, 5:15*am, GS wrote:
correction... The code goes in the code window behind ThisWorkbook. It will run every time the workbook is opened. * To access the VBE keyboard *Alt+F11 *and then expand the *'Microsoft Excel Objects' *folder. Double-click on *'ThisWorkbook' *and paste the code. ***Make sure you edit *<sheetname *to match the name of the sheet you want this to affect*** **Remove this codeifyou put it anywhere other than in ThisWorkbook** -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hi! Thank you so much for the explanation and taking the time to share your knowledge...and it works! It was driving me mad! I wanted to add another cell (M10 again but on another worksheet in the same workbook into the code can you advise on how I do this. I tried this but it gave me an error! Option Explicit Private Sub Workbook_Open() With Sheets("<sheetname" and "<sheetname").Range("M10") If .Value = "" Then .Value = Date End With End Sub Many thanks in advance LC |
If cell is blank automatically enter today's date in the same cell
LCTECH001 explained on 1/17/2012 :
On Jan 18, 5:15*am, GS wrote: correction... The code goes in the code window behind ThisWorkbook. It will run every time the workbook is opened. * To access the VBE keyboard *Alt+F11 *and then expand the *'Microsoft Excel Objects' *folder. Double-click on *'ThisWorkbook' *and paste the code. ***Make sure you edit *<sheetname *to match the name of the sheet you want this to affect*** **Remove this codeifyou put it anywhere other than in ThisWorkbook** -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hi! Thank you so much for the explanation and taking the time to share your knowledge...and it works! It was driving me mad! I wanted to add another cell (M10 again but on another worksheet in the same workbook into the code can you advise on how I do this. I tried this but it gave me an error! Option Explicit Private Sub Workbook_Open() With Sheets("<sheetname" and "<sheetname").Range("M10") If .Value = "" Then .Value = Date End With End Sub Many thanks in advance LC Thanks for the feedback, ..much appreciated! You'll need to do each sheet separately so just copy/paste the code for 1 sheet and replace the sheetname accordingly. If there are several sheets then we can set up a loop so one block of code works for all sheets in a list. Let me know if this would be better and I'll post new code. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
If cell is blank automatically enter today's date in the same cell
On Jan 18, 1:05*pm, GS wrote:
LCTECH001 explained on 1/17/2012 : On Jan 18, 5:15*am, GS wrote: correction... The code goes in the code window behind ThisWorkbook. It will run every time the workbook is opened. * To access the VBE keyboard *Alt+F11 *and then expand the *'Microsoft Excel Objects' *folder. Double-click on *'ThisWorkbook' *and paste the code. ***Make sure you edit *<sheetname *to match the name of the sheet you want this to affect*** **Remove this codeifyou put it anywhere other than in ThisWorkbook** -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hi! Thank you so much for the explanation and taking the time to share your knowledge...and it works! It was driving me mad! I wanted to add another cell (M10 again but on another worksheet in the same workbook into the code can you advise on how I do this. I tried this but it gave me an error! Option Explicit Private Sub Workbook_Open() * With Sheets("<sheetname" and "<sheetname").Range("M10") * * If .Value = "" Then .Value = Date * End With End Sub Many thanks in advance LC Thanks for the feedback, ..much appreciated! You'll need to do each sheet separately so just copy/paste the code for 1 sheet and replace the sheetname accordingly. If there are several sheets then we can set up a loop so one block of code works for all sheets in a list. Let me know if this would be better and I'll post new code. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - Hi GS, Another speedy response from you I see, thank you its very much appreciated! I copy and pasted the example code below the original one and it came back with another error!! Do you mean select the page behind the actual worksheet and place the new code in? How will this work if the original code for “Package Summary Report” is on “This Worksheet” code sheet and the copied code for “Package Activity Report” is on its corresponding code sheet? Hope I’m making sense! Option Explicit Private Sub Workbook_Open() With Sheets("Package Summary Report").Range("M10") If .Value = "" Then .Value = Date End With End Sub Option Explicit Private Sub Workbook_Open() With Sheets("Package Activity Report").Range("M10") If .Value = "" Then .Value = Date End With End Sub Best Regards LC |
If cell is blank automatically enter today's date in the same cell
You probable get an error message of 'ambiguous' or 'duplicate'
something or other. You can't have 2 procedures with the same name in a single project. Also, there is no 'ThisWorksheet'! It's 'ThisWorkbook', and the code should be like this... Option Explicit Private Sub Workbook_Open() With Sheets("Package Summary Report").Range("M10") If .Value = "" Then .Value = Date End With With Sheets("Package Activity Report").Range("M10") If .Value = "" Then .Value = Date End With End Sub Double-click (in the VBE) each worksheet listed with 'ThisWorkbook' and delete all code not being used for other purposes. HTH -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
If cell is blank automatically enter today's date in the same cell
On Jan 19, 12:04*am, GS wrote:
You probable get an error message of 'ambiguous' or 'duplicate' something or other. You can't have 2 procedures with thesamename in a single project. Also, there is no 'ThisWorksheet'! It's 'ThisWorkbook', and the code should be like this... Option Explicit Private Sub Workbook_Open() * With Sheets("Package Summary Report").Range("M10") * *If.Value = "" Then .Value =Date * End With * With Sheets("Package Activity Report").Range("M10") * *If.Value = "" Then .Value =Date * End With End Sub Double-click (in the VBE) each worksheet listed with 'ThisWorkbook' and delete all code not being used for other purposes. HTH -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hi Garry, Brilliant! I'm so grateful for all your assistance. These forums are just amazing and so good to know there is help out there when you feel you've exhausted your knowledge and you don't know where else to turn! Have a great day and I imagine we'll cross paths again with my never ending excel conundrums! Regards LC |
If cell is blank automatically enter today's date in the same cell
If you have a lot of sheets to do this for, you can do something like
this in the code window for 'ThisWorkbook'... Option Explicit Const sDateRanges As String = _ "Package Summary Report:M10,Package Activity Report:M10" Private Sub Workbook_Open() Dim v As Variant, v1 As Variant For Each v In Split(sDateRanges, ",") v1 = Split(v, ":") With Sheets(v1(0)).Range(v1(1)) If .Value = "" Then .Value = Date End With Next 'v End Sub ...where sDateRanges is a comma delimited string of value pairs. The value pairs are delimited with a colon. -- If you want to do this for every sheet in the workbook then it would be best to give the cell to get the date a local defined name so you can ref the same cell regardless of its actual address location. This means the cell address does NOT have to be "M10" on every sheet. Example: Each date cell on every sheet is named "DateRange" via the Define Name dialog as follows: Select the cell to get the date if empty at startup In the name box type: 'Package Summary Report'!DateRange Click OK Select the next sheet and repeat for each, substituting the text between the parenthesis (single quotes) with the actual sheet's name. Now you can use the following code to set the date for all sheets... Option Explicit Private Sub Workbook_Open() Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets With wks.Range("DateRange") If .Value = "" Then .Value = Date End With Next 'wks End Sub -- -OR- you could do a combination of these by adding the defined name to each sheet as described and leaving the date range out of the string, as follows, if you only want to do this to specific sheets as in the first example. Option Explicit Const sDateRanges As String = _ "Package Summary Report,Package Activity Report" Private Sub Workbook_Open() Dim v As Variant For Each v In Split(sDateRanges, ",") With Sheets(v).Range("DateRange") If .Value = "" Then .Value = Date End With Next 'v End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
If cell is blank automatically enter today's date in the same cell
LCTECH001 formulated the question :
On Jan 19, 12:04*am, GS wrote: You probable get an error message of 'ambiguous' or 'duplicate' something or other. You can't have 2 procedures with thesamename in a single project. Also, there is no 'ThisWorksheet'! It's 'ThisWorkbook', and the code should be like this... Option Explicit Private Sub Workbook_Open() * With Sheets("Package Summary Report").Range("M10") * *If.Value = "" Then .Value =Date * End With * With Sheets("Package Activity Report").Range("M10") * *If.Value = "" Then .Value =Date * End With End Sub Double-click (in the VBE) each worksheet listed with 'ThisWorkbook' and delete all code not being used for other purposes. HTH -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hi Garry, Brilliant! I'm so grateful for all your assistance. These forums are just amazing and so good to know there is help out there when you feel you've exhausted your knowledge and you don't know where else to turn! Have a great day and I imagine we'll cross paths again with my never ending excel conundrums! Regards LC You're welcome! Glad to be of help... Did you see my latest offering below? It gives you some abreviated methods for doing multiple sheets! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
If cell is blank automatically enter today's date in the same cell
On Jan 19, 8:21*am, GS wrote:
LCTECH001 formulated the question : On Jan 19, 12:04*am, GS wrote: You probable get an error message of 'ambiguous' or 'duplicate' something or other. You can't have 2 procedures with thesamename in a single project. Also, there is no 'ThisWorksheet'! It's 'ThisWorkbook', and the code should be like this... Option Explicit Private Sub Workbook_Open() * With Sheets("Package Summary Report").Range("M10") * *If.Value = "" Then .Value =Date * End With * With Sheets("Package Activity Report").Range("M10") * *If.Value = "" Then .Value =Date * End With End Sub Double-click (in the VBE) each worksheet listed with 'ThisWorkbook' and delete all code not being used for other purposes. HTH -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hi Garry, Brilliant! I'm so grateful for all your assistance. These forums are just amazing and so good to know there is help out there when you feel you've exhausted your knowledge and you don't know where else to turn! Have a great day and I imagine we'll cross paths again with my never ending excel conundrums! Regards LC You're welcome! Glad to be of help... Did you see my latest offering below? It gives you some abreviated methods for doing multiple sheets! -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - Hi Garry, Wow! This VB code stuff is quite addictive!! I was quite pleased when I manged to write a successful formula but now you've opened up a whole new world of problem solving!!! Thank you for the additional information. I'm going to try it on a test sheet so I can learn more about what VB can do and hopefully become better at it! Very helpful! Regards L |
If cell is blank automatically enter today's date in the same cell
on 1/18/2012, LCTECH001 supposed :
Hi Garry, Wow! This VB code stuff is quite addictive!! I was quite pleased when I manged to write a successful formula but now you've opened up a whole new world of problem solving!!! Thank you for the additional information. I'm going to try it on a test sheet so I can learn more about what VB can do and hopefully become better at it! Very helpful! Regards I appreciate the feedback... Best wishes! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
| All times are GMT +1. The time now is 03:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com