Require Variable to Hold Scope over 24 hour period only

[WednesdayC]

Hi All

Can anyone help me please.

I have a Public count variable, whose value is in a cell on a Worksheet, which clicks up automatically on calling a certain macro. What I am trying to achieve is that even if the Workbook is closed down and reopened, provided the date has not clicked over to the next day, the Count variable will continue to click up, whenever the macro is called.

However whenever the date clicks over to the next day, the Count variable should reset to 0.

I am not sure how to achieve this.

Thanks in advance.

Wednesday

 

[Ruddles]

Store the date in an adjacent cell. When the macro runs, check the date in the adjacent cell: if it's the same as today's date, increment the counter; if it's different to todays's date, reset the counter to zero.

 

[Snakehips]

WednesdayC,

Perhaps with your count in eg cell A2 and the date it was last re-set in B2.....
Excel 2007

	A	B
1	Count	Date Set
2	45	13/04/2013

<TBODY>
</TBODY>

<TBODY>
</TBODY>

The following code on opening.

Private Sub Workbook_Open()
If Date > Sheets("Sheet1").Range("B2") Then
Sheets("Sheet1").Range("A2") = 0
Sheets("Sheet1").Range("B2") = Date
End If
'set yor variable
PublicCountVar = Sheets("Sheet1").Range("A2")
End Sub

Hope that helps

 

[WednesdayC]

Hi Ruddles

Thank you for your reply.
Of course, it seems so obvious now.
Just what I need.

Regards
Wednesday

 

[WednesdayC]

Hi Snakehips
Love the Name!

Thanks for your help. Both you and Ruddles have provided the answer I need.

Regards
Wednesday