Thursday, February 12, 2015

Freezing Panes...Lessons learned...PLUS a patch before getting to the right solution

The Issue

This past week email from Allen, one of his readers, Steven, asked how to make frozen panes carry forward when opening a new window.  Following is my answer, plus some.

....and I said, here's an easy bit of code.

To the best of my knowledge there is no setting to carry over the frozen panes of an active worksheet to a new window.  The following code will set it to what Steven said he needs, top row and leftmost column frozen.  
  • The first If statement prevent the code from executing if the Sheet is not a Worksheet, e.g. a ChartSheet
  • The second If statement prevents re-setting the first row/column if the Window already has frozen rows/columns set
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    With Wn
        If .ActiveSheet.Type <> xlWorksheet Then Exit Sub
        If Not .FreezePanes Then
            .SplitColumn = 1
            .SplitRow = 1
            .FreezePanes = True
        End If
    End With
End Sub

To emulate the current settings, i.e. if a new window is created for a worksheet with different point of freezing, then the solution is much more complex, but based on the above.  

I closed with "Sorry, but I was not able to work through all of that before sending this."

So I thought about it some more!

Standard Freeze for New Sheets

Since the standard for this workbook is the top row and the leftmost column,it is also possible to freeze the panes when creating a new worksheet.  The constants are declared at the Workbook Module level to allow them to be used in the across all of the Window event procedures.  (More of that below)


Const StdWinRowLock  As Double = 1 ' Number of rows to be frozen at top: _
                            0 = No rows frozen; 1 = Top row frozen; etc..
Const StdWinColLock As Double = 1 ' Number of Columns to be frozen at right: _
                            0 = No Columns frozen; 1 = Leftmost Column frozen; etc..
Const StdFreezePanes As Boolean = True

Private Sub Workbook_NewSheet(ByVal Sh As Object) 'Set like most recent
    With Sh
        If .Type <> xlWorksheet Then Exit Sub
        With ActiveWindow
            If LastWinFreezePanes Then
                ' Use first set of variables if you want to make new sheet split like most recent
                ' Use commented out constants if you want new sheets to follow a standard
                .SplitColumn = StdWinColLock
                .SplitRow = StdWinRowLock
                .FreezePanes = StdFreezePanes
            End If
        End With
    End With
End Sub

Copying current Window settings to a New Window

The base question has a more complex answer than the one given above.  Suppose a worksheet has multi-row or multi-column headers and the need is to make the new window look like the existing one, i.e. if the top two rows and two leftmost columns are frozen in the worksheet then the new window should be frozen the same way.  Here is a summary of the challenges

User Requirements

  • There are several actions the user takes that trigger the code
    • Adding a Window
    • Closing a Window
    • Changing from one Window to another
    • Adding a Worksheet
    • Adding a sheet that is not a Worksheet, e.g. a Chart.  (There are two others.)
    • Changing from one sheet to another.
  • Each of the above triggers a series of events wherein data that might be more useful downstream is available only upstream.
  • Another user action, one that does not trigger an event but affects the solution, is changing rows/columns that are frozen.
  • Provide for creating a new worksheet either based on the most recently used worksheet or using a standard number of frozen rows and columns.

Technical Challenges

  • Data must be shared between event procedures, capturing the data in a preceding procedure then using it in one that follows in the stream.
  • The exposed object model does not include ...
    • ...a Workbook_NewWindow event, as it does a Workbook_NewSheet.  In this case the solution will need to carry the settings from the existing Window to the new one.  
    • ... a way to see the freeze points of more than one worksheet at a time.   Only the ActiveSheet property of the ActiveWindow has these properties.
  • When adding a new sheet, capturing then applying the freeze values cannot be done in a single event procedure and the needed data cannot be captured in a preceding procedure to be passed to one that is later in the stream. 
    • Events are in the following order
      1. Workbook_NewSheetParameter  "sh as Object" refers to the new sheetActiveWindow.ActiveSheet also refers to the new sheet
      2. Workbook_SheetDeactivate
        Parameter  "sh as Object" refers to the sheet being deactivated
        ActiveWindow.ActiveSheet refers to the new sheet
      3. Workbook_SheetActivate
        Parameter  "sh as Object" refers to the new sheet
        ActiveWindow.ActiveSheet also refers to the new sheet
  • For all sheets except Excel 5.0 Dialog sheets, ActiveWindowActiveSheet.Type is a valid property.

Annotated Solution

The following code is in the Workbook object module

Before any Subs or Functions, include the following declarations

Option Explicit

Dim WkbkWindowsCount As Integer ' Used to identify when new Windows opened and existing Windows closed
Dim IsNewWindow As Boolean ' Used to communicate when a new window has been opened.
Dim IsNewSheet As Boolean ' Used to communicate when a new sheet is created.

' Following communicate the pertinent Freeze properties of the most recently used worksheet
Dim LastWinRowSplit As Double
Dim LastWinColSplit As Double
Dim LastWinFreezePanes As Boolean

' Following are the standards for a new worksheet's Freeze settings.
Const StdWinRowLock  As Double = 1 ' Number of rows to be frozen at top: _
                            0 = No rows frozen; 1 = Top row frozen; etc..
Const StdWinColLock As Double = 1 ' Number of Columns to be frozen at right: _
                            0 = No Columns frozen; 1 = Leftmost Column frozen; etc..
Const StdFreezePanes As Boolean = True ' Setting this to False or setting the other two constants of this group to 0 will result in a worksheet that has no frozen panes.

' For the following two constants...
' FreezeNewLikeMostRecent has precedence.  If it is TRUE then the new worksheet will mimic the most recently active worksheet
Const FreezeNewLikeMostRecent As Boolean = True
Const FreezeNewToStandard As Boolean = False

In addition to the Workbook event procedures, there are two Subs to be included.  They were created to consolidate redundant code.


Sub FreezeNewWindow(wn As Window)
    With wn
        If FreezeNewLikeMostRecent Then
            .SplitColumn = LastWinColSplit
            .SplitRow = LastWinRowSplit
            .FreezePanes = LastWinFreezePanes
        Else
            If FreezeNewToStandard Then
                .SplitColumn = StdWinColLock
                .SplitRow = StdWinRowLock
                .FreezePanes = StdFreezePanes
            End If
        End If
    End With
End Sub

Sub SetLastWinFreeze(wn As Window)
    With wn
        LastWinColSplit = .SplitColumn
        LastWinRowSplit = .SplitRow
        LastWinFreezePanes = .FreezePanes
    End With
End Sub

Problem realized!

It was at this point of writing this entry I realized where I had failed.  In short, what the following code does is set the worksheet's freeze properties in the new window the same as in the originating Window.  It does not set the all of the worksheets the same as in the original window.

Semi-fixed

Below is the rest of the code.  The real solution will take a much different form, but there was much learned in developing this far.

Private Sub Workbook_NewSheet(ByVal sh As Object) 'Set like most recent
  ' Used to communicate to ActivateSheet event procedure instead of duplicating code here
    IsNewSheet = True 
End Sub

Private Sub Workbook_SheetActivate(ByVal sh As Object)
    If Not ActiveWindow.FreezePanes Then
        If IsNewSheet Then
            Call FreezeNewWindow(ActiveWindow)
            IsNewSheet = False
        End If
    End If
    Call SetLastWinFreeze(ActiveWindow)
End Sub

Private Sub Workbook_SheetDeactivate(ByVal sh As Object)
    Dim ActiveWinSheetName As String
    On Error Resume Next 
    ' On error logic needed to handle MS_Excel 5.0 Dialog sheets which do not have the TYPE property
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    ActiveWinSheetName = ActiveWindow.ActiveSheet.Name
    sh.Activate
    If sh.Type = xlWorksheet Then
        If Err.Number = 0 Then
            If ActiveWindow.FreezePanes <> LastWinFreezePanes _
                    Or ActiveWindow.SplitColumn <> LastWinColSplit _
                    Or ActiveWindow.SplitRow <> LastWinRowSplit Then ' Catches if Freeze changed since activation
                Call SetLastWinFreeze(ActiveWindow)
            End If
        End If
    End If
    Sheets(ActiveWinSheetName).Activate
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    On Error GoTo 0
End Sub

The WindowActivate event procedure was modified to warn the user not to close this one.  Unfortunately, the only place to provide a warning is when the window is activated, i.e. there is no WindowClose event and by the time the WindowDeactivate even procedure is triggered the ActiveWindow is set to the successor.

Private Sub Workbook_WindowActivate(ByVal wn As Window)
    ' Triggered when _
        * Switch from Window to an Existing Window _
        * Switch from Window to a New Window _
        * Closing Window without closing workbook
    Dim sh As Object
    With wn
 ' ********************************************************************************************************
' The following message was added to provide a warning to the user that this        
        If Right(wn.Caption, 2) = ":1" Then
            MsgBox Prompt:="You are now accessing the primary window for " & wn.Caption & "." _
                        & Chr(10) & Chr(10) & "If you close this window before " _
                        & IIf(wn.Parent.Windows.Count = 2, "the other open window ", "any of the " _
                        & wn.Parent.Windows.Count - 1 & " other open windows")  _
                       & ", you will lose your freeze panes settings.", _
                   Buttons:=vbExclamation
        End If
 ' ********************************************************************************************************
        If .Parent.Windows.Count < WkbkWindowsCount Then ' Closed Window
            WkbkWindowsCount = .Parent.Windows.Count
        Else
            If IsNewWindow Then
                Call FreezeNewWindow(wn)
                For Each ws In wn.Parent.Sheets
                    If sh.Name <> wn.ActiveSheet.Name Then
                        sh.Activate
                        sh.Visible = False
                    End If
                Next ws
                IsNewWindow = False
            End If
        End If
    End With
    On Error GoTo 0
End Sub

Private Sub Workbook_WindowDeactivate(ByVal wn As Window)
    ' Triggered when _
        * Switch 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
        Else
            IsNewWindow = False
        End If
        Call SetLastWinFreeze(wn)
    End With
End Sub