Freeze Panes via VBA selects wrong freeze point

[passinthru]

This is weird. I have a macro which I recorded, then modified. The first thing it does is to freeze the top row, then it goes looking for a particular row and inserts some formulae. Nothing complicated at all.

It WAS working perfectly, but I wanted some improvements in the insertion of the formulae. I got that working just fine, but now the panes freeze in the wrong place. No matter what I do, it freezes at cell I16. I want only the top row frozen.

I searched the forum here, and found this code which was reported to work:

    ActiveWindow.FreezePanes = False
    Range("A1").Select
    ActiveWindow.FreezePanes = True

Nope. It still freezes at I16. I have tried shutting down Excel, and even my computer, in case it is some weird bug where something is stuck in memory. No help.

I tried recording another macro to format some cells and also freeze the top row. Same result. Here's that code, en toto:

Sub wraptext_top_row()
'
' wraptext_top_row Macro
'

'
    Rows("1:1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

    Range("A1").Select
    ActiveWindow.FreezePanes = True
End Sub

Same result. I tried copying the contents of the worksheet to Notepad, then into a fresh workbook, just in case there's some weird hangup in formatting that I can't see. No help.

I'm baffled. Any ideas? There is NOTHING in the original code that references that cell, or even that row or column. NOTHING. How it got hung up on that one cell I cannot fathom.



The Freeze Panes command works normally if I apply it manually. Any cell, anywhere, it works as expected.

 

[passinthru]

Hmmm. I still don't know why it broke, but I think I have it fixed. I recorded a macro using the "Freeze Top Row" command (instead of just "Freeze Panes"), which gave me this code:

    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True

That still didn't work. It just froze some things at some random row, depending on what the active cell was at the time I ran the macro. (That's NOT what the command is expected to do!)

So, I added a line to first select cell A1, and it now works.

    Range("A1").Select
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True

This behavior seems to be a bug with the Freeze Panes command. I should add that I'm using Excel 2010 under Win7 64 bit.

 

[BillieDee]

I was able to utilize this response in a vb2013 application that I'm developing. Thank you! The freezepane issue was driving me crazy.

 

[PatrickWW]

Thanks for the great post. I actually omitted the Range and it worked perfectly for me.

Thanks!

 

[ian0411]

Rows("2:2").Select
ActiveWindow.FreezePanes = True

This is the easiest way to freeze the top row. The rule for FreezePanes is it will freeze the upper left corner from the cell you selected. For example, if you highlight C10, it will freeze between columns B and C, rows 9 and 10. Also, the .SplitColumn or .SplitRow will split your window once you unfreeze it which is not the way I like.

 

[silicon_beaver]

Try turning on
Application.ScreenUpdating = True
Application.DisplayAlerts = True

It should work properly now.

Most VBA developers turn off them to speed up macro running.
While FreezePanes requests them on. Otherwise, FreezePanes will go default, where is screen vision center, not your macro setup.

You can re-turn off them after FreezePanes done.

 

[bernatbosch]

Coding with both a structured worksheet (with rows and/or columns grouped) may be tricky.

I have spent more than an entire hour to understand what was wrong with my code, and finally I think I've got it.

My problem was that in the moment that my code was trying to freeze the window in panes, the groups moved around without myself having noticed it. Therefore, at the very moment that my freeze panes function was "shooting", the top 10 rows of my worksheet were actually out of the visible range (of course, everything inside a routine running as fast as the wind and with excel powers —such as screen updating— being turned off etc.) which caused an issue because these rows always remained like blocked out of the visible range after running the procedure. Really annoying!

So I made a little research on the web and was trying several options until I've got it.

That was my solution:

'correct steps to freeze panes programmatically with vba
'1. expand all the groups in the active sheet
ActiveSheet.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8 'Expand all levels
'2.scroll up to the row that must be saved as the first in the window or window pane
ActiveWindow.ScrollRow = 1
'3.select the right cell to apply the freeze panes (freeze will follow the top left limits of that cell)
[C24].Select
'4.apply freeze panes function
ActiveWindow.FreezePanes = True

'collapse back all the groups in the active sheet
ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 'Collapse all levels (=1) (to expand the first level use number 2)

By the way: I'm Excel for Mac 2011 user.

Enjoy!

 

[siti sal]

Ya, never thought of this..

Try turning on
Application.ScreenUpdating = True
Application.DisplayAlerts = True

It should work properly now.

Most VBA developers turn off them to speed up macro running.
While FreezePanes requests them on. Otherwise, FreezePanes will go default, where is screen vision center, not your macro setup.

You can re-turn off them after FreezePanes done.

 

[mellymelle]

Coding with both a structured worksheet (with rows and/or columns grouped) may be tricky.

I have spent more than an entire hour to understand what was wrong with my code, and finally I think I've got it.

My problem was that in the moment that my code was trying to freeze the window in panes, the groups moved around without myself having noticed it. Therefore, at the very moment that my freeze panes function was "shooting", the top 10 rows of my worksheet were actually out of the visible range (of course, everything inside a routine running as fast as the wind and with excel powers —such as screen updating— being turned off etc.) which caused an issue because these rows always remained like blocked out of the visible range after running the procedure. Really annoying!

So I made a little research on the web and was trying several options until I've got it.

That was my solution:

'correct steps to freeze panes programmatically with vba
'1. expand all the groups in the active sheet
ActiveSheet.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8 'Expand all levels
'2.scroll up to the row that must be saved as the first in the window or window pane
ActiveWindow.ScrollRow = 1
'3.select the right cell to apply the freeze panes (freeze will follow the top left limits of that cell)
[C24].Select
'4.apply freeze panes function
ActiveWindow.FreezePanes = True

'collapse back all the groups in the active sheet
ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 'Collapse all levels (=1) (to expand the first level use number 2)

By the way: I'm Excel for Mac 2011 user.

Enjoy!

Thank you Berna! Was having the same issue and had to resort to manually ensuring my cursor was on row to be frozen when executing macro as a workaround. The scrollrow worked like a charm. Your post is the first i've come across that addresses this specific issue.