Require Variable to Hold Scope over 24 hour period only

WednesdayC

Board Regular
Joined
Nov 7, 2010
Messages
201
Office Version
  1. 2016
Platform
  1. MacOS
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.
 
Upvote 0
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.

Code:
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
 
Last edited:
Upvote 0
Hi Ruddles

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

Regards
Wednesday
 
Upvote 0
Hi Snakehips
Love the Name!

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

Regards
Wednesday
 
Upvote 0

Forum statistics

Threads
1,219,094
Messages
6,146,268
Members
450,681
Latest member
ASBM24

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top