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

 

[Skyybot]

The code that is in the Original ThisWorkbook module, does it use the ThisWorkbook or ActiveWorkbook reference? If you have more than one Workbook open you'll need to specify by name so that code won't run on other Workbooks. You can use an IF statement in the code to make sure it only runs on that Workbook.

If Workbooks.Name = "YourWorkbookName" Then
'Your code here
End If

 

[sspatriots]

The code that is in the Original ThisWorkbook module, does it use the ThisWorkbook or ActiveWorkbook reference? If you have more than one Workbook open you'll need to specify by name so that code won't run on other Workbooks. You can use an IF statement in the code to make sure it only runs on that Workbook.

If Workbooks.Name = "YourWorkbookName" Then
'Your code here
End If

Thanks. I’ll give this a look and get back.

 

[sspatriots]

The code that is in the Original ThisWorkbook module, does it use the ThisWorkbook or ActiveWorkbook reference? If you have more than one Workbook open you'll need to specify by name so that code won't run on other Workbooks. You can use an IF statement in the code to make sure it only runs on that Workbook.

If Workbooks.Name = "YourWorkbookName" Then
'Your code here
End If

Started to do this and then realized that the workbook name will change frequently. That is, this workbook that I'm wanting to fire the code on for the window resize event is being used as a template that project managers will copy from a folder and rename the file to the job name they are working on. I was sort of trying to figure that out with my last post. Event though I got through that question and made that work, I couldn't get it to fit into this bigger picture of the problem I have described above. I didn't know if there was a way to store that workbook name as a public variable or something as soon as that original file is opened and refer to that as the "wbk" in the windwow resize event.

 

[Skyybot]

I used a Static variable. It seems to work the way you want.

Private Sub Workbook_WindowResize(ByVal Wn As Window)
Static x As Workbook
Set x = ThisWorkbook

If ThisWorkbook.Name = x.Name Then
    MsgBox "First Book"    'Your code here
End If

End Sub

 

[sspatriots]

This is what I have after your suggestion, however, when I open the second file while this is minimized the code tries to past a value in C4 of the file that opened.

Private Sub Workbook_WindowResize(ByVal Wn As Window)

Dim CurrentlyElapsed As Variant, ElapsedTime As Variant

Static x As Workbook
Set x = ThisWorkbook

If ThisWorkbook.Name = x.Name Then

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

        CurrentlyElapsed = Now() - x.Sheets("Sheet1").Range("E1").Value

        ElapsedTime = CurrentlyElapsed + x.Sheets("Sheet1").Range("C4").Value

        x.Sheets("Sheet1").Range("C4").Value = ElapsedTime

        StartTimer

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

        x.Sheets("Sheet1").Range("E1").Value = Now()

        StopTimer

    End If

End If

End Sub

 

[Skyybot]

Okay. I want to make sure that I'm operating under the same parameters as you. The Workbook_Resize sub is in a Template? Are you opening a second instance of that template, or just another instance of a Blank Excel file?

 

[sspatriots]

Okay. I want to make sure that I'm operating under the same parameters as you. The Workbook_Resize sub is in a Template? Are you opening a second instance of that template, or just another instance of a Blank Excel file?

Opening a different file altogether. Some of these guys have four or five different files open at a time and all files being unrelated. I just need figure out how to be able to minimize this original file that stops a timer temporarily and then turns it back on when it is maximized without that Window Resize event running on the other workbooks that are opened and seeing that event in the first workbook because it changes the other workbooks I open if this one is minimized. The plan is to let them save this template as whatever project name they are working on, so I was hoping to try and pin down the file name (whatever they save it as) to be the only file that sees the Window Resize event. Thanks SS

 

[Skyybot]

This worked for me.

Private Sub Workbook_WindowResize(ByVal Wn As Window)
Dim CurrentlyElapsed As Variant, ElapsedTime As Variant


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

        'CurrentlyElapsed = Now() - x.Sheets("Sheet1").Range("E1").Value

        'ElapsedTime = CurrentlyElapsed + x.Sheets("Sheet1").Range("C4").Value

        'x.Sheets("Sheet1").Range("C4").Value = ElapsedTime

        'StartTimer

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

        'x.Sheets("Sheet1").Range("E1").Value = Now()

        'StopTimer

    End If


End Sub

 

[Skyybot]

Actually, it seems the Minimize portion is not working. Stand by.