PDA

View Full Version : Create Time Log Based on Workbook.Activate/Deactivate events



accell
08-27-2013, 10:44 AM
I need help creating a time log on a new sheet that records a timestamp each time the workbook is activated and deactivated. Clarification: I want it to record each time I click in and out of a workbook, not each time it is exited/reopened. Thanks!

SamT
08-27-2013, 12:42 PM
I am providing code for all four events. You can use them as you like. I am setting the code to use the cell in column A below the last used cell in B for the Opening time, B for the Activation time, C for the Deactivation time and the Cell in D in the same row as last Activation for the Close time.

All this code goes into the ThisWorkbook Code Pane.

The code logic is that the Open and Activate Events always get recorded in the row after the last Activate Event, but the Deactivate and Close events always get recorded in the same row as the last Activate Event.

You do not need to use the Open and Close subs, all record Cells are chosen from the Cell with the last Activate Event record.


Option Explicit

'Format the columns on RecordSheet to Date/Time as desired

Const RecordSheet As Object = Sheets("Sheet1") 'Change Sheet name to suit
Const BkActCol As String = "B" 'Column that holds the Workbook Activate time

Private Sub Workbook_Open()
RecordSheet.Cells(Rows.Count, BkActCol).End(xlUp).Offset(1, -1).Value = Now
End Sub

Private Sub ThisWorkbook_WindowActivate(ByVal Wn As Excel.Window)
RecordSheet.Cells(Rows.Count, BkActCol).End(xlUp).Offset(1, 0).Value = Now
End Sub

Private Sub ThisWorkbook_WindowDeactivate(ByVal Wn As Excel.Window)
RecordSheet.Cells(Rows.Count, BkActCol).End(xlUp).Offset(0, 1).Value = Now
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
RecordSheet.Cells(Rows.Count, BkActCol).End(xlUp).Offset(0, 2).Value = Now
End Sub

The results should look like
OpenTime ActivateTime DeactivateTime CloseTime
OpenTime ActivateTime DeactivateTime CloseTime
OpenTime ActivateTime DeactivateTime CloseTime

The last Row might look like

OpenTime ActivateTime DeactivateTime CloseTime

accell
08-27-2013, 01:31 PM
This looks like it will be perfect If I can get it to execute correctly. I am getting an error that reads "Invalid Data Type For Constant" and the debugger highlights "Const RecordSheet As Object".I can't figure out how to correct this, any ideas? Thanks for your time

SamT
08-27-2013, 01:53 PM
:doh: My bad. That's what happens when I post code without debugging it first.

Change the Const Line to
Const RecordSheet As String = "Sheet1" 'Change Sheet name to suitAnd in each sub change "RecordSheet" to
Sheets(RecordSheet)

accell
08-27-2013, 02:29 PM
Everything seems to be working now except for the activate/deactivate subs. I can not get them to record when I click in and out of the workbook or switch workbooks (Only the open/close subs are working). Is it possible that there is a setting in excel that is preventing these updates? Thanks

SamT
08-27-2013, 03:14 PM
Goes to show, the help files are, ... uh... I mean I am not always accurate. After experimenting


Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
MsgBox "Activated"
Sheets(RecordSheet).Cells(Rows.Count, BkActCol).End(xlUp).Offset(1, 0).Value = Now
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
Sheets(RecordSheet).Cells(Rows.Count, BkActCol).End(xlUp).Offset(0, 1).Value = Now
End Sub

accell
08-27-2013, 04:28 PM
Thanks SamT, that works great! I do have one more question though, is there anyway to attach the deactivate event to whether or not the Excel window is being displayed (i.e if the window is minimized it will deactivate and reactivate upon maximizing OR if the Excel window is hidden behind another window it won't be logging time)? I assume you can't do this but just thought I'd ask. Thanks

accell
08-27-2013, 05:05 PM
I tried taggin on this code and it didn't work but I thought it might be headed in the right direction.


Private Sub Form_Minimize()
If Me.WindowState = FormWindowState.Minimized Then
Sheets(RecordSheet).Cells(Rows.Count, BkActCol).End(xlUp).Offset(0, 1).Value = Now
End If
End Sub

Private Sub Form_Maximize()
If Me.WindowState = vbMaximized Then
MsgBox "Activated"
Sheets(RecordSheet).Cells(Rows.Count, BkActCol).End(xlUp).Offset(1, 0).Value = Now
End If
End Sub

SamT
08-27-2013, 05:56 PM
Private Sub Form_Minimize()
Are you talking about a UserForm?

UserForms do not have Maximize and Minimize Events.

Try replacing the names of the activate subs above with the simple "Workbook_Activate()" and "Workbook_Deactivate()" to see if that is closer to what you want.

Another Event to look at might be the

Private Sub Workbook_WindowResize(ByVal Wn As Excel.Window) You can use it to check the window state and set a global Boolean Flag variable. Then check the value of the flag when another Event occurs that you do/don't want to record the time of.

accell
08-28-2013, 10:04 AM
I did not mean to create a user form, more so I just don't know what i'm doing.

Here is what I'm trying with no luck:

Private Sub Workbook_WindowResize(ByVal Wn As Excel.Window)
If Wn.WindowState = xlMaximized Then
MsgBox "Activated"
Sheets(LogSheet).Cells(Rows.Count, BkActCol).End(xlUp).Offset(1, 0).Value = Now
End If
End Sub

I don't know if the 'IF' statement is structured correctly or if expression 'wn' is the correct one to put before the 'WindowState'. Also, you mentioned creating a Boolean Flag variable, would that be embedded in this specific resize sub or up where the constants are defined?

accell
08-28-2013, 12:10 PM
Hey, I realized that this is working but only if you resize the workbook window within Excel. Is there a way to write this code to reference the window size of the whole Excel application? Thanks!

SamT
08-28-2013, 12:33 PM
In VBA, type the word "application" on any code page, and, while the cursor is in or touching the word, press F1.

This will bring up the help file. Look for the link to the Application Object and follow it.

On the Application Object help page, look in the Events drop down for various Resize and (De)Activate events. You can use the Back Arrow at the top or right-click in the help page and select "Back" to navigate.

Be sure to look at the WindowResize Event help and compare it to the last examples I gave you if you are thinking of Application_Resize.