Doing Time

[Elsie]

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

 

[Mala]

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]

Thank you so very much. Just what i needed.

Elsie

 

[Elsie]

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