If cell is blank automatically enter today's date in the same cell

LCTECH001 · Jan 11, 2012

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. Ihave 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

GS · Jan 11, 2012

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

LCTECH001 · Jan 11, 2012

LCTECH001 explained :

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

GS · Jan 12, 2012

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

GS · Jan 12, 2012

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

Gord Dibben · Jan 12, 2012

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

GS · Jan 12, 2012

Gord Dibben brought next idea :

You should re-think this statement GS

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...

GS · Jan 12, 2012

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!

LCTECH001 · Jan 16, 2012

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

GS · Jan 17, 2012

LCTECH001 laid this down on his screen :

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**

Cimjet · Jan 17, 2012

Hi Garry
I think you meant to say >To access the VB editor press Alt+F11 not Ctrl+F11
Best regards
Cimjet

GS · Jan 17, 2012

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...

GS · Jan 17, 2012

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

LCTECH001 · Jan 18, 2012

correction...

To access the VBE keyboard Alt+F11 and then expand the 'Microsoft
Excel

--
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

GS · Jan 18, 2012

LCTECH001 explained on 1/17/2012 :

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.

LCTECH001 · Jan 18, 2012

LCTECH001 explained on 1/17/2012 :

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

GS · Jan 18, 2012

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

LCTECH001 · Jan 19, 2012

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

GS · Jan 19, 2012

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

GS · Jan 19, 2012

LCTECH001 formulated the question :

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!