Insert to today's date

You have got the right idea...
However make B3 empty. If you change it to 0. 0 will be populated against
the date...
You can write the code in the worksheet_activate event (signature below)
associated with the sheet containing B3
Private Sub Worksheet_Activate()
'Put your code here
'Let me know if you want me to do this
'put an IF around the line where earlier code assigns a value
'use that to change B3 to ""
End Sub

"SPISO" wrote:

One more question. Is there a way to revise B3 in Dashboard to change to 0
the first time the workbook is opened any given day?

I am thinking something like if the cell associated to the date in 'Draw' is
empty, make B3 = 0. Then once B3 is changed it will insert a value in the
associated date in 'Draw' from your previous macro preventing B3 from
changing to 0 again that day.

Would that work?

"Sheeloo" wrote:

You are most welcome.

That is what computers are meant for.... saving us from tedious work.

"SPISO" wrote:

Thanks So Much. It worked. What a time saver.

I understand why I should not put a zero in B3. That makes since. I am
going to have to plead ignorant on what you were talking about with the code
in worksheet_activate event comment though. I am going to need help with
that.

You have been such a life saver.


"Sheeloo" wrote:

You have got the right idea...
However make B3 empty. If you change it to 0. 0 will be populated against
the date...
You can write the code in the worksheet_activate event (signature below)
associated with the sheet containing B3
Private Sub Worksheet_Activate()
'Put your code here
'Let me know if you want me to do this
'put an IF around the line where earlier code assigns a value
'use that to change B3 to ""
End Sub

"SPISO" wrote:

One more question. Is there a way to revise B3 in Dashboard to change to 0
the first time the workbook is opened any given day?

I am thinking something like if the cell associated to the date in 'Draw' is
empty, make B3 = 0. Then once B3 is changed it will insert a value in the
associated date in 'Draw' from your previous macro preventing B3 from
changing to 0 again that day.

Would that work?

"Sheeloo" wrote:

You are most welcome.

That is what computers are meant for.... saving us from tedious work.

"SPISO" wrote:

Thanks So Much. It worked. What a time saver.

Right-click on the Dashboard tab, choose View Code and paste the following
either before or after the earlier macro

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("Draw").Select
Sheets("Draw").Columns("A:A").Select
Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Offset(0, 1).FormulaR1C1 = "" Then
Sheets("Dashboard").Cells(3, 2) = ""
End If
Sheets("Dashboard").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


'Note: Application.EnableEvents has to be turnedoff (as done in the code) so
that same event is not fired again and again as the code reads the other sheet

"SPISO" wrote:

I understand why I should not put a zero in B3. That makes since. I am
going to have to plead ignorant on what you were talking about with the code
in worksheet_activate event comment though. I am going to need help with
that.

You have been such a life saver.


"Sheeloo" wrote:

You have got the right idea...
However make B3 empty. If you change it to 0. 0 will be populated against
the date...
You can write the code in the worksheet_activate event (signature below)
associated with the sheet containing B3
Private Sub Worksheet_Activate()
'Put your code here
'Let me know if you want me to do this
'put an IF around the line where earlier code assigns a value
'use that to change B3 to ""
End Sub

"SPISO" wrote:

One more question. Is there a way to revise B3 in Dashboard to change to 0
the first time the workbook is opened any given day?

I am thinking something like if the cell associated to the date in 'Draw' is
empty, make B3 = 0. Then once B3 is changed it will insert a value in the
associated date in 'Draw' from your previous macro preventing B3 from
changing to 0 again that day.

Would that work?

"Sheeloo" wrote:

You are most welcome.

That is what computers are meant for.... saving us from tedious work.

"SPISO" wrote:

Thanks So Much. It worked. What a time saver.

It worked but I was not shocked that it worked after all your other helps
worked as well.

Let's say I wanted to use the same code but do it for yesterday and two days
ago. I would still want to associate the value next to the dates in "Draw".

Could one change your code from =Date to something like =Date-1 and =Date-2
or something like that? Or would it be better to have yesterday's date in a
cell on the dashboard such as =Today()-1 in A4 and refer to that cell in your
code to associate it with the value in worksheet 'Draw' where the value from
'Draw' can be copied to B4 on the 'Dashboard'?

"Sheeloo" wrote:

Right-click on the Dashboard tab, choose View Code and paste the following
either before or after the earlier macro

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("Draw").Select
Sheets("Draw").Columns("A:A").Select
Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Offset(0, 1).FormulaR1C1 = "" Then
Sheets("Dashboard").Cells(3, 2) = ""
End If
Sheets("Dashboard").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


'Note: Application.EnableEvents has to be turnedoff (as done in the code) so
that same event is not fired again and again as the code reads the other sheet

"SPISO" wrote:

I understand why I should not put a zero in B3. That makes since. I am
going to have to plead ignorant on what you were talking about with the code
in worksheet_activate event comment though. I am going to need help with
that.

You have been such a life saver.


"Sheeloo" wrote:

You have got the right idea...
However make B3 empty. If you change it to 0. 0 will be populated against
the date...
You can write the code in the worksheet_activate event (signature below)
associated with the sheet containing B3
Private Sub Worksheet_Activate()
'Put your code here
'Let me know if you want me to do this
'put an IF around the line where earlier code assigns a value
'use that to change B3 to ""
End Sub

"SPISO" wrote:

One more question. Is there a way to revise B3 in Dashboard to change to 0
the first time the workbook is opened any given day?

I am thinking something like if the cell associated to the date in 'Draw' is
empty, make B3 = 0. Then once B3 is changed it will insert a value in the
associated date in 'Draw' from your previous macro preventing B3 from
changing to 0 again that day.

Would that work?

"Sheeloo" wrote:

You are most welcome.

That is what computers are meant for.... saving us from tedious work.

"SPISO" wrote:

Thanks So Much. It worked. What a time saver.

You can use Date - 1 or Date - 2... Excel treats a date as number of days
from 1/1/1900 so all mathematical operations are possible.

You have a programmer's mind... :-)

"SPISO" wrote:

It worked but I was not shocked that it worked after all your other helps
worked as well.

Let's say I wanted to use the same code but do it for yesterday and two days
ago. I would still want to associate the value next to the dates in "Draw".

Could one change your code from =Date to something like =Date-1 and =Date-2
or something like that? Or would it be better to have yesterday's date in a
cell on the dashboard such as =Today()-1 in A4 and refer to that cell in your
code to associate it with the value in worksheet 'Draw' where the value from
'Draw' can be copied to B4 on the 'Dashboard'?

"Sheeloo" wrote:

Right-click on the Dashboard tab, choose View Code and paste the following
either before or after the earlier macro

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("Draw").Select
Sheets("Draw").Columns("A:A").Select
Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Offset(0, 1).FormulaR1C1 = "" Then
Sheets("Dashboard").Cells(3, 2) = ""
End If
Sheets("Dashboard").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


'Note: Application.EnableEvents has to be turnedoff (as done in the code) so
that same event is not fired again and again as the code reads the other sheet

"SPISO" wrote:

I understand why I should not put a zero in B3. That makes since. I am
going to have to plead ignorant on what you were talking about with the code
in worksheet_activate event comment though. I am going to need help with
that.

You have been such a life saver.


"Sheeloo" wrote:

You have got the right idea...
However make B3 empty. If you change it to 0. 0 will be populated against
the date...
You can write the code in the worksheet_activate event (signature below)
associated with the sheet containing B3
Private Sub Worksheet_Activate()
'Put your code here
'Let me know if you want me to do this
'put an IF around the line where earlier code assigns a value
'use that to change B3 to ""
End Sub

"SPISO" wrote:

One more question. Is there a way to revise B3 in Dashboard to change to 0
the first time the workbook is opened any given day?

I am thinking something like if the cell associated to the date in 'Draw' is
empty, make B3 = 0. Then once B3 is changed it will insert a value in the
associated date in 'Draw' from your previous macro preventing B3 from
changing to 0 again that day.

Would that work?

"Sheeloo" wrote:

You are most welcome.

That is what computers are meant for.... saving us from tedious work.

"SPISO" wrote:

Thanks So Much. It worked. What a time saver.

Thanks again for all your help. I could not have done this without you.

"Sheeloo" wrote:

You can use Date - 1 or Date - 2... Excel treats a date as number of days
from 1/1/1900 so all mathematical operations are possible.

You have a programmer's mind... :-)

"SPISO" wrote:

It worked but I was not shocked that it worked after all your other helps
worked as well.

Let's say I wanted to use the same code but do it for yesterday and two days
ago. I would still want to associate the value next to the dates in "Draw".

Could one change your code from =Date to something like =Date-1 and =Date-2
or something like that? Or would it be better to have yesterday's date in a
cell on the dashboard such as =Today()-1 in A4 and refer to that cell in your
code to associate it with the value in worksheet 'Draw' where the value from
'Draw' can be copied to B4 on the 'Dashboard'?

"Sheeloo" wrote:

Right-click on the Dashboard tab, choose View Code and paste the following
either before or after the earlier macro

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("Draw").Select
Sheets("Draw").Columns("A:A").Select
Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Offset(0, 1).FormulaR1C1 = "" Then
Sheets("Dashboard").Cells(3, 2) = ""
End If
Sheets("Dashboard").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


'Note: Application.EnableEvents has to be turnedoff (as done in the code) so
that same event is not fired again and again as the code reads the other sheet

"SPISO" wrote:

I understand why I should not put a zero in B3. That makes since. I am
going to have to plead ignorant on what you were talking about with the code
in worksheet_activate event comment though. I am going to need help with
that.

You have been such a life saver.


"Sheeloo" wrote:

You have got the right idea...
However make B3 empty. If you change it to 0. 0 will be populated against
the date...
You can write the code in the worksheet_activate event (signature below)
associated with the sheet containing B3
Private Sub Worksheet_Activate()
'Put your code here
'Let me know if you want me to do this
'put an IF around the line where earlier code assigns a value
'use that to change B3 to ""
End Sub

"SPISO" wrote:

One more question. Is there a way to revise B3 in Dashboard to change to 0
the first time the workbook is opened any given day?

I am thinking something like if the cell associated to the date in 'Draw' is
empty, make B3 = 0. Then once B3 is changed it will insert a value in the
associated date in 'Draw' from your previous macro preventing B3 from
changing to 0 again that day.

Would that work?

"Sheeloo" wrote:

You are most welcome.

That is what computers are meant for.... saving us from tedious work.

"SPISO" wrote:

Thanks So Much. It worked. What a time saver.

I was thinking this weekend about my dashboard that you have helped me
create. Last week you wrote me a code that changed B3 to "" if the cell
associated to today's date = "" in 'draw'. I would like to use that same
blank cell in 'draw' to cause another action when the workbook opens.

I would like cell Q27 in 'Dashboard' to change to the value associated to
today in worksheet 'goals' if there isn't any value associated to today in
'Draw'. Would I just change "" to the sheet name and cell? Is that an easy
modification to the code you have below? What would that look like?

I did a side by side comparison to your two codes where one was caused by a
cell change versus a workbook opening. They do look pretty similar but I am
missing something when i combine the two to get the macro to input a cell
value versus "".



Right-click on the Dashboard tab, choose View Code and paste the following
either before or after the earlier macro

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("Draw").Select
Sheets("Draw").Columns("A:A").Select
Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Offset(0, 1).FormulaR1C1 = "" Then
Sheets("Dashboard").Cells(3, 2) = ""
End If
Sheets("Dashboard").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


'Note: Application.EnableEvents has to be turnedoff (as done in the code) so
that same event is not fired again and again as the code reads the other sheet

"SPISO" wrote:

I understand why I should not put a zero in B3. That makes since. I am
going to have to plead ignorant on what you were talking about with the code
in worksheet_activate event comment though. I am going to need help with
that.

You have been such a life saver.


"Sheeloo" wrote:

You have got the right idea...
However make B3 empty. If you change it to 0. 0 will be populated against
the date...
You can write the code in the worksheet_activate event (signature below)
associated with the sheet containing B3
Private Sub Worksheet_Activate()
'Put your code here
'Let me know if you want me to do this
'put an IF around the line where earlier code assigns a value
'use that to change B3 to ""
End Sub

"SPISO" wrote:

One more question. Is there a way to revise B3 in Dashboard to change to 0
the first time the workbook is opened any given day?

I am thinking something like if the cell associated to the date in 'Draw' is
empty, make B3 = 0. Then once B3 is changed it will insert a value in the
associated date in 'Draw' from your previous macro preventing B3 from
changing to 0 again that day.

Would that work?

"Sheeloo" wrote:

You are most welcome.

That is what computers are meant for.... saving us from tedious work.

"SPISO" wrote:

Thanks So Much. It worked. What a time saver.