View settings are not preserved in "New Window"

[piannetta]

Hi everyone, I'm finding it really annoying that when I open a new window (View -> New Window) so I can work with two different sections of the same workbook, that the new window doesn't seem to preserve the view settings of the workbook. So for example, if I have frozen panes on a sheet, when I view that sheet in the new window, those panes are not frozen.

what's even more annoying (and yes, this is on me) but if I accidentally close the original window instead of the new window then exit the workbook, ALL of my original view settings are lost and I have to go through every sheet and restore them manually.

Is there any way to stop this from happening, or to have Excel preserve the original view settings when opening a new window?

Cheers,
Pete

 

[jkpieterse]

Unfortunately, view settings are part of the window object. To ensure you keep your view settings, make sure you first close the window that has "2" after its name in the title bar.

 

[piannetta]

Unfortunately, view settings are part of the window object. To ensure you keep your view settings, make sure you first close the window that has "2" after its name in the title bar.

Thanks for the explanation.

I understand how to avoid it, I'm just trying to avoid those moments where I close the wrong window first

 

[jkpieterse]

I wouldn't know a way to prevent this from happening. There is a WindowDeactivate event, but it doesn't have a cancel argument to stop closing the window in question.

 

[Jaafar Tribak]

A couple of win32 api calls can come to the rescue in such particular scenario: (A bit hacky but should do the trick)

Code in the ThisWorkbook Module:

Option Explicit

'Code prevents main workbook window (that has ":1" after its name) from closing.
'The window can only be closed last ie: when there is only 1 workbook window open.
 
#If VBA7 Then
    Private Declare PtrSafe Function GetSystemMenu Lib "user32" (ByVal hWnd As LongPtr, ByVal bRevert As Long) As Long
    Private Declare PtrSafe Function DeleteMenu Lib "user32" (ByVal hMenu As LongPtr, ByVal nPosition As Long, ByVal wFlags As Long) As LongPtr
#Else
    Private Declare Function GetSystemMenu Lib "user32" (ByVal hWnd As Long, ByVal bRevert As Long) As Long
    Private Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long
#End If

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    If Not Wn.Caption Like "*:1" And Windows.Count = 1 Then
        Cancel(Wn) = False
    End If
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    If Wn.Caption Like "*:1" And Windows.Count > 1 Then
        Cancel(Wn) = True
    End If
End Sub

Private Property Let Cancel(ByVal Wn As Window, ByVal vNewValue As Boolean)
    Const SC_CLOSE = &HF060
    If vNewValue Then
        Call DeleteMenu(GetSystemMenu(Wn.hWnd, 0&), SC_CLOSE, 0&)
    Else
        Call GetSystemMenu(Wn.hWnd, 1&)
    End If
End Property

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Debug.Print "Closing..."
End Sub

 

[jkpieterse]

Devious. It does not stop me from using control+F4 to close the window though

 

[Jaafar Tribak]

Thanks jkpieterse.

I had that in mind but somehow I forgot to add it to the code ... Following is an update of the above code which caters for closing with the Alt+F4

Again, in the ThisWorkbook Module:

Option Explicit

'Code prevents main workbook window (that has ":1" after its name) from being closed.
'The window can only be closed last ie: when there is only 1 workbook window open.
 
#If VBA7 Then
    Private Declare PtrSafe Function GetSystemMenu Lib "user32" (ByVal hWnd As LongPtr, ByVal bRevert As Long) As LongPtr
    Private Declare PtrSafe Function DeleteMenu Lib "user32" (ByVal hMenu As LongPtr, ByVal nPosition As Long, ByVal wFlags As Long) As LongPtr
#Else
    Private Declare Function GetSystemMenu Lib "user32" (ByVal hWnd As Long, ByVal bRevert As Long) As Long
    Private Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long
#End If

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    If Not Wn.Caption Like "*:1" And Windows.Count = 1 Then
        Cancel(Wn) = False
    End If
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    If Wn.Caption Like "*:1" And Windows.Count > 1 Then
        Cancel(Wn) = True
    End If
End Sub

Private Property Let Cancel(ByVal Wn As Window, ByVal vNewValue As Boolean)
    Const SC_CLOSE = &HF060
    If vNewValue Then
        Call DeleteMenu(GetSystemMenu(Wn.hWnd, 0&), SC_CLOSE, 0&)
        Application.OnKey "%{F4}", Me.CodeName & ".CloseNonMainWindow"
    Else
        Call GetSystemMenu(Wn.hWnd, 1&)
        Application.OnKey "%{F4}"
    End If
End Property

Private Sub CloseNonMainWindow()
    If Not ActiveWindow.Caption Like "*:1" And Windows.Count > 1 Then
        ActiveWindow.Close
    End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Debug.Print "Closing..."
End Sub

 

[Jaafar Tribak]

Ooops! Sorry. I misread jk's post.
I amended the code for hooking Alt+F4 (which is the standard system menu Key Combo for closing windows) but I forgot to do the same for Ctrl+F4

In the ThisWorkbook Module:

Option Explicit

'Code prevents main workbook window (that has ":1" after its name) from being closed.
'The window can only be closed last ie: when there is only 1 workbook window open.
  
#If VBA7 Then
    Private Declare PtrSafe Function GetSystemMenu Lib "user32" (ByVal hWnd As LongPtr, ByVal bRevert As Long) As LongPtr
    Private Declare PtrSafe Function DeleteMenu Lib "user32" (ByVal hMenu As LongPtr, ByVal nPosition As Long, ByVal wFlags As Long) As LongPtr
#Else
    Private Declare Function GetSystemMenu Lib "user32" (ByVal hWnd As Long, ByVal bRevert As Long) As Long
    Private Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long
#End If

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    If Not Wn.Caption Like "*:1" And Windows.Count = 1 Then
        Cancel(Wn) = False
    End If
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    If Wn.Caption Like "*:1" And Windows.Count > 1 Then
        Cancel(Wn) = True
    End If
End Sub

Private Property Let Cancel(ByVal Wn As Window, ByVal vNewValue As Boolean)
    Const SC_CLOSE = &HF060
    If vNewValue Then
        Call DeleteMenu(GetSystemMenu(Wn.hWnd, 0&), SC_CLOSE, 0&)
        HookAltKeys = True
    Else
        Call GetSystemMenu(Wn.hWnd, 1&)
        HookAltKeys = False
    End If
End Property

Private Property Let HookAltKeys(ByVal vNewValue As Boolean)
    With Application
        If vNewValue Then
            .OnKey "%{F4}", Me.CodeName & ".CloseNonMainWindow"
            .OnKey "^{F4}", Me.CodeName & ".CloseNonMainWindow"
        Else
            .OnKey "%{F4}"
            .OnKey "^{F4}"
        End If
    End With
End Property

Private Sub CloseNonMainWindow()
    If Not ActiveWindow.Caption Like "*:1" And Windows.Count > 1 Then
        ActiveWindow.Close
    End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    HookAltKeys = False
    Debug.Print "Closing..."
End Sub

 

[jkpieterse]

@Jaafar Tribak Note that on my system (Win 11, Office 365) the window caption numbering is different, it ends with " - 1", not with ":1".

 

[Jaafar Tribak]

@Jaafar Tribak Note that on my system (Win 11, Office 365) the window caption numbering is different, it ends with " - 1", not with ":1".

Thank you for letting us know this ... That seems to be also the excel edition the OP is using (showing on his forum profile).
In that case, a simple change in the code of each instance of Wn.Caption Like "*:1" to (Wn.Caption Like "*:1" Or Wn.Caption Like "* - 1") should cover both excel editions ... Something along these lines:

In the ThisWorkbook Module:

Option Explicit

'Code prevents main workbook window (that has ":1" after its name) from being closed.
'The window can only be closed last ie: when there is only 1 workbook window open.
  
#If VBA7 Then
    Private Declare PtrSafe Function GetSystemMenu Lib "user32" (ByVal hWnd As LongPtr, ByVal bRevert As Long) As LongPtr
    Private Declare PtrSafe Function DeleteMenu Lib "user32" (ByVal hMenu As LongPtr, ByVal nPosition As Long, ByVal wFlags As Long) As LongPtr
#Else
    Private Declare Function GetSystemMenu Lib "user32" (ByVal hWnd As Long, ByVal bRevert As Long) As Long
    Private Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long
#End If

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    If Not (Wn.Caption Like "*:1" Or Wn.Caption Like "* - 1") And Windows.Count = 1 Then
        Cancel(Wn) = False
    End If
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    If (Wn.Caption Like "*:1" Or Wn.Caption Like "* - 1") And Windows.Count > 1 Then
        Cancel(Wn) = True
    End If
End Sub

Private Property Let Cancel(ByVal Wn As Window, ByVal vNewValue As Boolean)
    Const SC_CLOSE = &HF060
    If vNewValue Then
        Call DeleteMenu(GetSystemMenu(Wn.hWnd, 0&), SC_CLOSE, 0&)
        HookSysMenuCloseAccKeys = True
    Else
        Call GetSystemMenu(Wn.hWnd, 1&)
        HookSysMenuCloseAccKeys = False
    End If
End Property

Private Property Let HookSysMenuCloseAccKeys(ByVal vNewValue As Boolean)
    With Application
        If vNewValue Then
            .OnKey "%{F4}", Me.CodeName & ".CloseNonMainWindow"
            .OnKey "^{F4}", Me.CodeName & ".CloseNonMainWindow"
        Else
            .OnKey "%{F4}"
            .OnKey "^{F4}"
        End If
    End With
End Property

Private Sub CloseNonMainWindow()
    With ActiveWindow
        If Not (.Caption Like "*:1" Or .Caption Like "* - 1") And Windows.Count > 1 Then
            .Close
        End If
    End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    HookSysMenuCloseAccKeys = False
    Debug.Print "Closing..."
End Sub