Thursday, February 12, 2015

Freezing Panes...Getting it Right!

Introduction...How we got here

In Freezing Panes...Lessons learned...PLUS a patch before getting to the right solution I worked through an almost solution.  What was implied (apparently too subtly for my ears) was that when a new window is created in Excel the worksheet settings for Freeze and Split are not populated in the new window.  The result is that if the original window is closed before others all of these settings are lost.

In short, what I wrote was code that only pushed forward the same split/freeze as the most recent worksheet, meaning that, assuming a workbook with worksheets "Sheet1", "Sheet2", and "Sheet3"
  • Select Sheet1!B2, then click on the Freeze of the View menu to Freeze Panes
  • Do the same for Sheet2 and Sheet3
  • Click on the New Window icon and a new window is created, with the active worksheet's FreezePanes set like the original.
  • However, if you view the other sheets of the same window you will not see frozen panes.

What is needed

When a new Window is created by clicking the New Window icon of the View menu, the sheets in the new Window should have their panes set exactly like those in the originating Window.
Excel's View Menu
 Above is the beginning point:  The workbook is created, the panes frozen, and the View menu selected.  TheCaption reads exatly like the workbook name, in this case, "Freeze New Windows 3 - All Sheets - Completed.xlsm".

When the New Window icon is clicked, a new Window is created.  The captions for the windows are different in that the original Window's caption is suffixed with ":1" and the new Window has a ":2" suffix.  
New Window created.  Both Windows side-by-side

New Approach

The previous approach was to capture the freeze-panes related information whenever changing focus from one window to another, and on a second level, from one worksheet to another.

After some review, and much trial-and-error, I realized that 

  • The For...Next structure could be used against the Windows collection, accessing the new Window and the original one. 
  • As before, Workbook event Subs, in this case WindowActivate and WindowDeactivate, could be leveraged.
  • There still needed to be communication between the different event-triggered Subs
  • A data structure (complex data type) would be useful as an array to gather the originating Window's FreezePane settings and applying them to the new Window's SheetViews
  • The data structure needed to be in a normal Module, not in the Workbook's object module.
  • Setting the FreezePane parameters was done when the Sheet was active
  • Activating the Sheet as Workbook.Sheets() did not provide the proper access to setting the FreezePane parameters.
  • To get the needed access to the FreezePane parameters, it was necessary to activate the Sheet as a member of the Window.ActiveSheets collection.
  • When clicking on the New Window icon of the View menu, the subsequent events are, in order
    1. The New Window is created
    2. The originating Window is deactivated
    3. The new Window is activated
  • As the solution is running, Application.EnableEvents must be set to false because the code activates the originating Window, and then re-activates the new Window. 

The Code

Module: Types

The following data type was defined in a normal module named "Types".  The name of the module is unimportant.  That the module is "normal", rather than the Workbook's object module, is necessary.

Option Explicit

Public Type WnWsPane
    SheetName As String
    SplitRow As Double
    SplitColumn As Double
    FreezePanes As Boolean
    Split As Boolean
End Type

Module: ThisWorkBook

Module-scoped data

The following module-level data declarations were included to allow the subordinate event-triggered subs to communicate.


Option Explicit

Dim wnwsPanes() As WnWsPane

Dim WkbkWindowsCount As Integer ' Used to identify when new Windows opened and existing Windows closed

Dim NeedToCaptureWnWsSettings As Boolean
Dim NeedToApplyWnWsSettings As Boolean

Dim IsNewSheet As Boolean ' Used to communicate when a new sheet is created.
Dim IsNewWindow As Boolean ' Used to communicate when a new window has been opened.

Event-Triggered Subs

As mentioned above, two Workbook event-triggered Subs come into play for this solution, Workbook_WindowActivate and Workbook_WindowDeactivate.
Each has "wn as Window" as a parameter, referring to the Window that is being activated or deactivated, respectively.

Also important in this solution is knowledge that the ActiveWindow is the newly created Window during the execution of both event-triggered Subs.  Understanding this exposes the need for the code to start by saving which window is active because the code also activates the original Window and the worksheets.

The following code is built in the ThisWorkBook object module

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    ' Triggered after the DeactiveWindow event when _
        * Creating a new window _
        * Switching from one Window to another Window of the same workbook _
        * Closing a Window of a Workbook
    With Wn
        If WkbkWindowsCount = 0 Then
            WkbkWindowsCount = .Parent.Windows.Count
        Else
        If .Parent.Windows.Count < WkbkWindowsCount Then ' Closed Window
                WkbkWindowsCount = .Parent.Windows.Count
            Else
                If IsNewWindow Then
                    Call ApplyWnPaneSettings(Wn)
                    IsNewWindow = False
                End If
            End If
        End If
    End With
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    ' Triggered before the ActivateWindow event when _
        * Switching from Window to an Existing Window _
        * Switch from Window to a New Window _
        * Closing Window
    With Wn
        If .Parent.Windows.Count > WkbkWindowsCount Then 'New Window
            WkbkWindowsCount = .Parent.Windows.Count
            IsNewWindow = True
            Call InitiateSheetPanesArray(Wn)
        Else
            IsNewWindow = False
        End If
    End With
End Sub

Additional Code

Two Subs were created to 
  1. Create an array of the Freeze Pane settings of the original Window, called during the DeactiveWindow Sub
  2. Apply the settings to the SheetView.Sheets of the new window, called during the WindowActivate Sub.
These may both be callable from the DeactiveWindow Sub but for clarity I decided they made more sense aligned as they are.

Private Sub InitiateSheetPanesArray(Wn As Window)
    Dim i As Integer
    Dim j As Integer
    Dim shView As Object
    Dim shVwCurr As Object
    Dim wnCurr As Window
    
    ReDim wnwsPanes(1 To Wn.Parent.Worksheets.Count)
    
    Application.EnableEvents = False
    Set wnCurr = ActiveWindow
    Set shVwCurr = Wn.ActiveSheetView
    
    Wn.Activate
    j = 0
    With ActiveWindow
        For i = 1 To .SheetViews.Count
            Set shView = .SheetViews(i)
            .SheetViews(i).Sheet.Activate
            On Error Resume Next
            If TypeName(.SheetViews(i).Sheet) = "Worksheet" Then
                ' TypeName() = "Worksheet" used instead of the .Type property because it results in _
                  excluding DialogSheets and Chart sheets, the only kind of sheets without FreezePane/Split.
                If Err.Number = 0 Then
                    j = j + 1
                    wnwsPanes(j).SheetName = .ActiveSheet.Name
                    wnwsPanes(j).Split = .Split
                    wnwsPanes(j).SplitColumn = .SplitColumn
                    wnwsPanes(j).SplitRow = .SplitRow
                    wnwsPanes(j).FreezePanes = .FreezePanes
                End If
            End If
            On Error GoTo 0
        Next i
    End With
    shVwCurr.Sheet.Activate
    wnCurr.Activate
    Application.EnableEvents = True
End Sub

Private Sub ApplyWnPaneSettings(Wn As Window) '
    Dim i As Integer
    Dim wnCurr As Window
    Dim shVwCurr As Object

    Application.EnableEvents = False
    Set wnCurr = ActiveWindow
    Set shVwCurr = wnCurr.ActiveSheetView
    Wn.Activate
    For i = LBound(wnwsPanes) To UBound(wnwsPanes)
        Wn.SheetViews(wnwsPanes(i).SheetName).Sheet.Activate
        ActiveWindow.Split = wnwsPanes(i).Split
        ActiveWindow.SplitColumn = wnwsPanes(i).SplitColumn
        ActiveWindow.SplitRow = wnwsPanes(i).SplitRow
        ActiveWindow.FreezePanes = wnwsPanes(i).FreezePanes
    Next i
    wnCurr.Activate

    shVwCurr.Sheet.Activate
    Application.EnableEvents = True
End Sub