excel vba freeze pane without select

Question

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. This is my current code which freezes rows 1–7 but uses Range.Select:

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

Any suggestions?

score 64 · Accepted Answer · 2015-12-04T18:42:55.320

64

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.

edited Dec 04 '15 at 18:42

answered Dec 04 '15 at 18:26

5

Nicely done! For everyone else, note that the SplitColumn/SplitRow values represent the last cell above/left of the split. So to freeze rows 1 through 7 and separate them from rows 8 onward, the code looks like this: `With ActiveWindow .SplitColumn = 0 .SplitRow = 7 End With ActiveWindow.FreezePanes = True` – ChrisB Dec 04 '15 at 18:34
1

I was thinking that something like `If .FreezePanes Then .FreezePanes = False` might be appropriate as the first line inside the `With ActiveWindow` block. – Dec 04 '15 at 18:37
I would agree with that @Jeeped otherwise I don't think it will freeze at another cell/range. First you have to unfreeze, then freeze. – David Zemens Dec 04 '15 at 18:38
Jeeped, I found it isn't necessary to first unfreeze the panes. If it's already split, your code just change the split point. – ChrisB Dec 04 '15 at 18:41
Good to know @ChrisB! Thanks for clarifying. – Dec 04 '15 at 18:46
Aren't you still selecting in a way because you're assuming an active window. – Archimedes Trajano Aug 26 '16 at 14:07
8

You can avoid using ActiveWindow if you instead access the Window object via Application.Windows("[window name]"), where [window name] is the Workbook.Name – Loophole Sep 22 '16 at 02:13
1

@Loophole indexing `Application.Windows()` by the workbook name is wrong and leads to `Run-time error '9' Subscript out of range`, see https://stackoverflow.com/a/47177498/1026 – Nickolay Nov 08 '17 at 10:44
This was helpful in my case where using win32com (python), where the Excel window starting opening open minimized in a way that the grid wasn't visible, and so selecting a range wouldn't work. – MarioTheHedgehog Nov 30 '18 at 05:34
2

Code causes bug, when you try to freeze and are you have a cell activated far away from where you intend to freeze. I intend to freeze the first column and a number of rows and the rows are correctly frozen, but for the columns the first visible column is frozen, which is arbitrary. If I then manually unfreeze, it remains as split screen. I tried to add activate before the code, which had no effect (probably because I turned Screenupdate off to speed up my macro)... I then tried the solution posted by z32a7ul and that worked. I guess what makes the difference is the adding of Scroll. – blablubbb Mar 23 '21 at 09:21
One negative side effect of this technique is that it leaves the Split window state active so that, if you then decide to manually change the freeze-panes state to include a frozen column range on the left (for example), you can't do so unless you happen to notice that the Split state is active and know that you must first turn it off. In effect, that's a subtle "bug" in this technique, which can be very frustrating for the unaware or novice user. See Dannid's post at https://stackoverflow.com/questions/3232920/how-can-i-programmatically-freeze-the-top-row-of-an-excel-worksheet-in-excel-200. – pstraton Feb 03 '22 at 02:28

score 12 · Answer 2 · edited Dec 29 '22 at 14:11

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

Jon Dee · Answer 3 · 2019-01-27T17:10:07.197

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.

TaitK · Answer 4 · 2019-05-30T18:36:57.780

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.

Does not make sense. Seems to work OK, even if referenced window is not active. — Tuntable, Sep 22 '18 at 23:52

score 1 · Answer 5 · edited Feb 05 '19 at 21:23

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

score 1 · Answer 6 · edited Mar 03 '21 at 23:45

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.

score 1 · Answer 7 · answered Nov 03 '21 at 16:10

1

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

answered Nov 03 '21 at 16:10

SAL

115
6

Yes, this type of technique is the most reliable with the least unwanted side effects. (See my comment above, regarding **user4039065**'s post.) But I would also set **ActiveWindow.FreezePanes = False** before selecting cell A2, in case Freeze Panes is already set to some other state, which would prevent this code from producing the desired effect. – pstraton Feb 03 '22 at 02:37
Also, beware: instead of **Range("A2").Select**, it may be tempting to to do something like **Range("1:1").Select** or **Rows(1).Select**, but doing so actually creates a quad-window freeze-panes state. (With no apparent usefulness, so maybe an old Excel bug?) – pstraton Feb 03 '22 at 02:49

score 0 · Answer 8 · answered Apr 05 '21 at 19:38

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

score 0 · Answer 9 · answered Sep 24 '21 at 18:46

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.

For a more accurate comparison, freeze panes on separate sheets instead of over and over on the same worksheet. Any real world use of freezing panes many times would involve separate sheets. (I can think of one real world example: a large number of reports generated for different users.) Also, your `Select` method doesn't un-freeze panes first, so if panes were already frozen, even in a different location on the sheet, it doesn't do anything. I suspect the time was so fast because no actions were completed for loops 2–1000 but I'm curious to see if that's actually true. — ChrisB, Oct 01 '21 at 18:04

score 0 · Answer 10 · edited May 14 '23 at 08:36

0

I am using the: "excel vba freeze pane without select code".

The only change I made was to add an additional parameter to pass an object for the 'Application' part to TheApp as 'Application' was undefined in my test environment.

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

edited May 14 '23 at 08:36

pmacfarlane

3,057
1
7
24

answered May 08 '23 at 02:17

Edward Z.

11
2

excel vba freeze pane without select

10 Answers10

Linked

Related