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
- Workbook_NewSheetParameter "sh as Object" refers to the new sheetActiveWindow.ActiveSheet also refers to the new sheet
- Workbook_SheetDeactivate
Parameter "sh as Object" refers to the sheet being deactivated
ActiveWindow.ActiveSheet refers to the new sheet - 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
No comments:
Post a Comment