Insert to today's date
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.
|