Doing Time

Elsie

Board Regular
Joined
Jun 3, 2002
Messages
121
Hello All

I'm having trouble coding a routine, probably due to my scan knowledge of VBA. This is the sort of thing:

Check the system date.

If it has changed since last time routine run then clear the contents of an accumulator and go on to the next sentence. If it hasn't then add user input time in minutes to the accumulator . On shuting the spreadsheet show a message "You have entered xxx minutes today" (where xxx is the contents of the accumulator).

Can anyone give me a hand with this?

Many thanks.

Elsie
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Mala

MrExcel MVP
Joined
Apr 4, 2002
Messages
627
Try the following code:
Public AccumTime As Integer, SysDt As Date, OldSysDt As Date
Sub TimeAccum()
SysDt = Date
If OldSysDt = 0 Then OldSysDt = SysDt: AccumTime = 0
If OldSysDt = SysDt Then
On Error Resume Next
AddMin = Val(Application.InputBox("Minutes to add", "Add minutes"))
If AddMin > 0 Then AccumTime = AccumTime + AddMin
On Error GoTo 0
End If
End Sub

To show the accumulator value copy the following code to the ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox ("You have entered " + CStr(AccumTime) + " minutes today")
End Sub

It'll work correctly if the workbook is open all day, else you will need to store the accumulator value in a worksheet cell and modify the code accordingly
 

Elsie

Board Regular
Joined
Jun 3, 2002
Messages
121
Hi all

I am inputting time into Column E and not via an input box.

I can't get this routine to work except by using the Selection Change sub and even then it doesn't allow the before workbook close sub to access the stored number in AccumTime.

Can anyone help?

Many thanks.

Elsie
 

Forum statistics

Threads
1,144,292
Messages
5,723,537
Members
422,502
Latest member
barakgahtan

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
Top