Introduction...How we got here
In 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
- The New Window is created
- The originating Window is deactivated
- 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
- Create an array of the Freeze Pane settings of the original Window, called during the DeactiveWindow Sub
- 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
No comments:
Post a Comment