This Workbook event code for Workbook_WindowResize

[sspatriots]

Good morning,

Trying to use the event code above to fire when I minimize a screen and again when I maximize it does something else. I was under the impression that since I have this code in the "This Workbook" module that it only applied to the workbook that the code is in. However, if I minimize my workbook and open a different workbook and run some code while that one is sitting minimized, the WindowResize event in the first workbook starts to run and does things to the second workbook I opened that were intended to happen to the original workbook when I maximized it again. How do I avoid this? I'm basically using it to stop code from running while it is minimized and then start again when maximize on the first workbook. Any suggestions would be greatly appreciated.


Thanks, SS

 

[sspatriots]

I noticed that you declared the Start variable twice. The Public Start should be sufficient. Also, I would more explicitly define the Ranges with ThisWorkbook

A Module in the TimerTest Workbook?

Yes

 

[Skyybot]

Basically this is to keep track of the time when I minimize and stop the timer while minimized. So when restoring the window the timer takes the current time and subtracts the time it was minimized and then adds that to the value in C4 to capture the total time even while minimized. On the flip side, when the user closes the file at the end of the day and reopens the next day, they have the option to resume manually start the clock from where they left off or zero it out. Thanks, Steve

If you want to keep time, even while Minimized, why stop the timer at all until the Workbook is closed?

 

[sspatriots]

If you want to keep time, even while Minimized, why stop the timer at all until the Workbook is closed?

I stopped it because when I’m opening other workbooks and running code in them it would stop that timer automatically when I wanted it to keep running.

 

[Skyybot]

I stopped it because when I’m opening other workbooks and running code in them it would stop that timer automatically when I wanted it to keep running.

Are you wanting to record the amount of time that instance is open?

 

[sspatriots]

Are you wanting to record the amount of time that instance is open?

Basically, during the day, the user opens the task list called "TestTimer" in the morning. They press "Start" to start tracking their time. Then they minimize the screen. They may open multiple other worksheets during the day. When they are done with one of the task on this list, they maximize and the clock is still running (theoretically). When they are ready to collect the total time on that task, they select "Stop" and then "Reset". "Reset" will copy the total time to the clipboard and give the user the option to select a task and record the time in the total time column.

 

[Skyybot]

Why not record a Start time when start button is clicked, a Stop time when the stop button is pressed, and do the math?

 

[sspatriots]

Why not record a Start time when start button is clicked, a Stop time when the stop button is pressed, and do the math?

Sounds reasonably doable. I’ll give it a try.

 

[Skyybot]

Simple_Time_Recorder.xlsm

Microsoft Excel Workbook

1drv.ms

 

[sspatriots]

Hi,

Just wanted to come back and update all the code that I ended up with that made this work thanks to Skybot and others that pitched in on this post and my previous one. It's a combination of everyone's inputs.

I stumbled across another post in this forum where someone suggested adding the following line:

If Not ActiveWorkbook Is ThisWorkbook Then Exit Sub

So I put this at the beginning of every code in my workbook, both the "ThisWorkbook" module and a regular module called "Timer_Stopwatch".

Thanks again to everyone that helped. Much appreciated.


My "ThisWorkbook" code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Not ActiveWorkbook Is ThisWorkbook Then Exit Sub

    Range("D1").Value = Range("C4").Value

    StopTimer

End Sub

Private Sub Workbook_Open()
    
If Not ActiveWorkbook Is ThisWorkbook Then Exit Sub
 
    Range("E1").Value = Now()
  
    ResumeOrRestart

End Sub

Private Sub Workbook_Activate()

Dim CurrentlyElapsed As Variant, ElapsedTime As Variant

If Not ActiveWorkbook Is ThisWorkbook Then Exit Sub

    If ActiveWindow.WindowState = xlMaximized Then

'MsgBox "Maximized " & Wn.Caption

        CurrentlyElapsed = Now() - Range("E1").Value

        ElapsedTime = CurrentlyElapsed + Range("C4").Value

        Range("C4").Value = ElapsedTime

        StartTimer

    ElseIf ActiveWindow.WindowState = xlMinimized Then

'MsgBox "Minimized " & Wn.Caption

        Range("E1").Value = Now()

        StopTimer

        Parent.Application.WindowState = xlMinimized

    End If

End Sub

Private Sub Workbook_Deactivate()

If Not ActiveWorkbook Is ThisWorkbook Then Exit Sub

    If ActiveWindow.WindowState = xlMinimized Then

        Range("F1").Value = Now()

        StopTimer

    End If

End Sub

Private Sub Workbook_WindowResize(ByVal Wn As Window)

Dim CurrentlyElapsed As Variant, ElapsedTime As Variant

If Not ActiveWorkbook Is ThisWorkbook Then Exit Sub

    If Wn.WindowState = xlMaximized Then
'        MsgBox "Maximized " & Wn.Caption

        CurrentlyElapsed = Now() - Range("E1").Value

        ElapsedTime = CurrentlyElapsed + Range("C4").Value

        Range("C4").Value = ElapsedTime

        StartTimer

    ElseIf Wn.WindowState = xlMinimized Then
'        MsgBox "Minimized " & Wn.Caption

        Range("E1").Value = Now()

        StopTimer

    End If

End Sub

My "Timer_Stopwatch" code (regular module):

Option Explicit

' Written by Philip Treacy, My Online Training Hub
' https://www.myonlinetraininghub.com/timer-stopwatch-excel-vba
Public Start As Variant
Sub StartTimer()

    Dim Start As Variant, RunTime As Variant, CurrentlyElapsed As Variant
    Dim ElapsedTime As String
    
If Not ActiveWorkbook Is ThisWorkbook Then Exit Sub

    'Set the control cell to 0 and make it green
    Range("B1").Value = 0
    Range("C4").Interior.Color = 13561798 'Light Green
    Range("C4").Font.Color = 24832 'Dark Green

    'If Range("D1").Value <> 0 Then CurrentlyElapsed = Range("D1").Value
    
    Start = Time - Range("C4").Value  ' Set start time subtract existing count
    Debug.Print Start
    Do While Range("B1").Value = 0
        
        DoEvents    ' Yield to other processes.
 '       RunTime = Timer    ' current elapsed time
 '       CurrentlyElapsed = RunTime - Start + Range("D1").Value
        CurrentlyElapsed = Time - Start
'        ElapsedTime = Format(CurrentlyElapsed / 86400, "hh:mm:ss")
         ElapsedTime = Format(CurrentlyElapsed, "hh:mm:ss")
'        ElapsedTime = Format(CurrentlyElapsed / 86400, "hh:mm")
        'Display currently elapsed time in C4
        Range("C4").Value = ElapsedTime
        Application.StatusBar = ElapsedTime
            
    Loop
        
    Range("C4").Value = ElapsedTime
    Range("C4").Interior.Color = 13551615 'light Red
    Range("C4").Font.Color = 393372 'Dark Red
    Range("D1").Value = CurrentlyElapsed
    Application.StatusBar = False

End Sub

Sub StopTimer()

If Not ActiveWorkbook Is ThisWorkbook Then Exit Sub

    'Set the control cell to 1
    Range("B1").Value = 1

    Range("C4").Interior.Color = 13551615 'light Red
    Range("C4").Font.Color = 393372 'Dark Red

End Sub

Sub ResetTimer()

Dim myRange As Range
Dim CopyRange As Range
Dim answer As Integer

If Not ActiveWorkbook Is ThisWorkbook Then Exit Sub

Set CopyRange = Range("C4")

On Error Resume Next

answer = MsgBox("Would you like to capture this time for one of the listed task?", vbQuestion + vbYesNo + vbDefaultButton2, "Capture or Clear Time Shown")

If answer = vbYes Then
'  MsgBox "Yes"

Set myRange = Application.InputBox(Prompt:="Select Cell you want to capture your total time in.", Title:="Format Titles", Type:=8)

If myRange Is Nothing Then
    MsgBox "No selection made", vbCritical, "Input required"
    Exit Sub
End If

CopyRange.Value = (CopyRange * 24 * 60 * 60) / 3600
myRange.Value = CopyRange.Value

Else
'  MsgBox "No"
End If

    If Range("B1").Value > 0 Then
       
        'Set the control cell to 1
        Range("C4").Value = Format(0, "hh:mm:ss")
'        Range("C4").Value = Format(0, "hh:mm")
    Range("C4").Interior.Color = 13551615 'light Red
    Range("C4").Font.Color = 393372 'Dark Red
    Range("D1").Value = 0
    
    End If
    
End Sub

Sub ResumeOrRestart()

Dim Result As VbMsgBoxResult

If Not ActiveWorkbook Is ThisWorkbook Then Exit Sub

Result = MsgBox("Select 'Yes' to resume from your previous clocked time. Select 'No' to automatically 'RESET' the 'Stopwatch'", vbYesNo + vbQuestion)

    If Result = vbYes Then
    
            'MsgBox "You clicked Yes"
    
        If Range("B1").Value > 0 Then
           
            'Set the control cell to 1
            Range("C4").Value = Format(0, "hh:mm:ss")
            'Range("C4").Value = Format(0, "hh:mm")
            Range("C4").Interior.Color = 13551615 'light Red
            Range("C4").Font.Color = 393372 'Dark Red
        
        End If
   
        Range("C4").Value = Range("D1").Value
    
        Else:
        
            'MsgBox "You clicked No"
    
         If Range("B1").Value > 0 Then
           
            'Set the control cell to 1
            Range("C4").Value = Format(0, "hh:mm:ss")
            'Range("C4").Value = Format(0, "hh:mm")
            Range("C4").Interior.Color = 13551615 'light Red
            Range("C4").Font.Color = 393372 'Dark Red
            Range("D1").Value = 0
            
        End If
    
        Range("C4").Value = Range("E1").Value
      
    End If

End Sub

 

[sspatriots]

Sorry, one last correction I didn't pick up on until this morning. In the "Sub ResumeOrRestart", change the last line in the code.


From:

Range("C4").Value = Range("E1").Value

Range("B1").Value = 1