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

 

[piannetta]

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

Thank you so very much for your assistance, I really appreciate the effort you have gone into to help solve my problem

 

[Jaafar Tribak]

Thank you so very much for your assistance, I really appreciate the effort you have gone into to help solve my problem

Thanks for the feedback and I glad I could help