excel vba freeze pane without select

25

votes

I have a VBA script in Excel that freezes the panes of an Excel worksheet, but I'm curious to see if this is possible without first selecting a range. Here's by code now which freezes rows 1 through 7:

ActiveSheet.Range("A8").Select
ActiveWindow.FreezePanes = True

Any suggestions?

excelvba

60

votes

Record yourself using the View ► Freeze Panes ► Freeze Top Row command and this is what you get for .FreezePanes.

With ActiveWindow
    If .FreezePanes Then .FreezePanes = False
    .SplitColumn = 0
    .SplitRow = 1
    .FreezePanes = True
End With

So modifying the .SplitColumn and/or .SplitRow properties should do it for you regardless on what the ActiveCell property is.

6

votes

There are many things to get wrong about freezing panes. I add my own answer, so I will find it here, and won't have to reinvent it next time.

Public Sub FreezePanesAt(rngDataTopLeft As Range)
    Dim wndCurrent As Window: For Each wndCurrent In rngDataTopLeft.Worksheet.Parent.Windows
        With wndCurrent
            .FreezePanes = False
            If Not ((rngDataTopLeft.Row = 1) And (rngDataTopLeft.Column = 1)) Then
                .ScrollRow = 1
                .ScrollColumn = 1
                .SplitRow = rngDataTopLeft.Row - 1
                .SplitColumn = rngDataTopLeft.Column - 1
                .FreezePanes = True
            End If
        End With
    Next
End Sub

Example usage:

FreezePanesAt ThisWorkbook.Worksheets("Sheet1").Range("B3")
FreezePanesAt ThisWorkbook.Names("Header").RefersToRange

The input parameter is the top left cell of the bottom right pane; I think this is the most frequent use case: you know the range at which to split and don't care about which workbook / worksheet / window it is in
If the input parameter is in the first row / first cell but not A1, then there will be only two panes; A1 is a special case, however, Excel would split the window at center of the current view, I prevented this because I can't think of any case where this would be intended
It iterates through all Windows attached to the workbook / worksheet; indexing into Application.Windows (Windows(Thisworkbook.Name)) won't cause an error if you have more windows to the same workbook (the name would be "MyWorkbook:1"), or Excel attempted (which usually fails) to repair a workbook after a crash (the name would be "MyWorkbook [Repaired]")
It takes into consideration that panes may already be frozen and the user / another macro might have scrolled to a location in the workbook, and the top left cell in the window is not A1

3

votes

I found the previous answers only worked with some sheets when looping through tabs. I found the following code worked on every tab I looped through (target was a single workbook), despite which workbook was the activeworkbook.

The short of it:

With Application.Windows(DataWKB.Name) 
    Application.Goto ws.Cells(4, 5)
    .SplitColumn = 4
    .SplitRow = 3
    .FreezePanes = True
End With

The code as it is in my Sub: (be aware, I do a lot more formatting in this sub, I tried to strip that out and leave just the code needed here)

Sub Format_Final_Report()
Dim DataWKB As Workbook
Set DataWKB = Workbooks("Report.xlsx")
Dim ws As Worksheet

Dim tabCNT As Long
Dim tabName As String
tabCNT = DataWKB.Sheets.Count

For i = 1 To tabCNT
    Set ws = DataWKB.Worksheets(i)
    tabName = ws.Name


    With Application.Windows(DataWKB.Name)
        Application.Goto ws.Cells(4, 5)
        .SplitColumn = 4
        .SplitRow = 3
        .FreezePanes = True
    End With

Next i

End Sub

Hopefully, this will save someone some research time in the future.

1

votes

I need to be able to properly refreeze panes (when creating new windows, notably) without losing the activecell or messing up the visible range. It took a lot of playing around but I think I have something solid that works:

Sub FreezePanes(nbLignes As Integer, nbColonnes As Integer, Optional ByVal feuille As Worksheet)
    If feuille Is Nothing Then Set feuille = ActiveSheet Else feuille.Activate
    Error GoTo erreur
    With ActiveWindow
        If .View = xlNormalView Then
            If .FreezePanes Then .FreezePanes = False
            If .Split Then .Split = False

            .SplitColumn = nbColonnes
            .SplitRow = nbLignes

            If .Panes.Count = 4 Then 'rows and columns frozen
                .Panes(1).ScrollRow = 1
                .Panes(1).ScrollColumn = 1
                .Panes(2).ScrollRow = 1 'top right pane
                .Panes(3).ScrollColumn = 1 'bottom left pane
            ElseIf nbLignes > 0 Then .Panes(1).ScrollRow = 1
            ElseIf nbColonnes > 0 Then .Panes(1).ScrollColumn = 1
            Else: GoTo erreur
            End If

            .FreezePanes = True
        End If
    End With
    Exit Sub
erreur:
    Debug.print "Erreur en exécutant le sub 'FreezePanes " & nbLignes & ", " & nbColonnes & ", '" & feuille.Name & "' : code #" & Err.Number & Err.Description
End Sub

1

votes

I know this is old but I came across this tidbit that may be useful... as ChrisB stated, the SplitColumn/SplitRow values represent the last cell above/left of the split BUT of the currently visible window. So if you happen to have code like this:

Application.Goto Worksheets(2).Range("A101"), True
With ActiveWindow
 .SplitColumn = 0
 .SplitRow = 10
 .FreezePanes = True
End With

The split will be between rows 110 and 111 instead of 10 and 11.

edited for clarification and to add more information:
My point is that the values are offsets of the upper left cell, not an address of a cell. Therefore, ChrisB's Dec 4 '15 at 18:34 comment under the main answer only holds if row 1 is visible in the Activewindow.

A couple of other points on this:

using Application.goto doesn't necessarily put whichever cell you are trying to go to in the upper left
the cell that is put in the upper left when using .goto can depend on the size of the excel window, the current zoom level, etc (so fairly arbitrary)
it is possible to have the splits placed so that you can not see them or even scroll around in the visible window (if .FreezePanes = true). for example:

Application.Goto Worksheets(1).Range("A1"), True  
With ActiveWindow  
 .SplitColumn = 100  
 .SplitRow = 100  
 .FreezePanes = True  
End With

CETAB may be dealing with this in their answer.

1

votes

Yes, the ActiveWindow.ScrollRow = 1 and ActivWindow.ScrollColumn = 1 is a must for FreezePanes if your visible window does not include cell A1.

If you are freezing rows 1:3 by selecting row 4 or cell A4, and cell A3 is not visible, the FreezePanes function will freeze the window in the center of the visible window.

Also if cell B4 is selected, and column A is not visible, then only the rows 1:3 will be frozen (column A will not frozen). Similarly, if rows 1:3 are not visible, only column A will be frozen. If both column A and rows 1:3 are not visible, the FreezePanes function will freeze the window in the center of the visible window.

0

votes

Here is what i use...

Public Sub FreezeTopRowPane(ByRef MyWs As Excel.Worksheet, _
                            Optional ByVal AfterRowNr As Integer = 1)

Dim SavedWS As Worksheet
Dim SavedUpdating As Boolean

SavedUpdating = Application.ScreenUpdating      'save current screen updating mode

Set SavedWS = ActiveSheet                       'save current active sheet

Application.ScreenUpdating = False              'turn off screen updating
MyWs.Activate                                   'activate worksheet for panes freezing
ActiveWindow.FreezePanes = False                'turn off freeze panes in case 
With ActiveWindow
    .SplitColumn = 0                            'set no column to split
    .SplitRow = AfterRowNr                      'set the row to split, default = row 1
End With
ActiveWindow.FreezePanes = True                 'trigger the new pane freezing

SavedWS.Activate                                'restore previous (saved) ws as active

Application.ScreenUpdating = SavedUpdating      'restore previous (saved) updating mode

End Sub

0

votes

I did a timing test of Freezing using .Select vs .Activate. Here is the code

Dim numLoops As Long
Dim StartTime, LoopTime As Long
numLoops = 1000


Debug.Print ("Timing test of numloops:" & numLoops)

StartTime = Timer

For I = 0 To numLoops
        targetSheet.Activate
    With ActiveWindow
    If .FreezePanes Then .FreezePanes = False
        .SplitColumn = 2
        .SplitRow = 1
        .FreezePanes = True
    End With

Next I

LoopTime = Timer
Debug.Print ("Total time of activate method:" & Format((LoopTime - StartTime) / 86400, "hh:mm:ss"))
StartTime = Timer

For I = 0 To numLoops
        targetSheet.Select
        Application.Range("C2").Select
        Application.ActiveWindow.FreezePanes = True
Next I

LoopTime = Timer
Debug.Print ("Total time of select method:" & Format((LoopTime - StartTime) / 86400, "hh:mm:ss"))

And here are the results.

Timing test of numloops:1000 
Total time of activate method:00:00:39 
Total time of select method:00:00:01

As you can see, .Select is much faster.

0

votes

The problem with splitting is that if a user unfreezes panes, the panes will remain split. (I couldn't find a way to turn off split afterwards while keeping the panes frozen)

This may be too obvious/simple, but what if the current selection is simply saved and then re-selected afterwards?

Sub FreezeTopRow()
    
    'First save the current selection to go back to it later
    Dim rngOriginalSelection As Range
    Set rngOriginalSelection = Selection
    
    'Change selection to A2 to make .FreezePanes work
    ActiveSheet.Range("A2").Select
    ActiveWindow.FreezePanes = True

    'Change selection back to original
    rngOriginalSelection.Select

End Sub

excel vba freeze pane without select

9 Answers