Freezing Panes using VBA

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Hi guys,

Another VBA question.....


I am trying to freeze panes at D2 with the following code:


Code:
' Freeze panes
    Range("D2").Select
    ActiveWindow.FreezePanes = True

It only seems to work properly if i have A1 active before running the macro. If I have other cells active, it behaves strangely.

Note: I do have some "hide column code" in the macro too, but whether I freeze panes before or after the hiding. It reacts strangely.

Any suggestions?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
Freezing panes through the menus gives the same result though... if column A is the leftmost column on the screen and you select D2 and freeze panes, A:C are frozen as you scroll right.

If column D is the leftmost column on the screen and you select D2 and freeze panes, no columns are frozen as you scroll to the right.

Selecting (or activating) A1 first is a way of ensuring you get the former.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Hi Oaktree,

I guess I understand when you explain it that way.... I should have thought of that.....

So should I be changing my code to:

Code:
' Freeze panes
    Range("A1").Select
    Range("D2").Select
    ActiveWindow.FreezePanes = True

Is this proper?


EDIT: Actually, even that doesn't work. How can I make it work?
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
I think I found my answer here: http://www.vbforums.com/showthread.php?p=2368375#post2368375 from someone with the handle "Webtest"

'If you need to reposition the view so that a particular cell is in the top left corner:
'Application.Goto Range("$G$6"), True 'Force Scroll to indicated cell


and applied it as such:

Code:
  ' Freeze panes
    Application.Goto Range("$A$1"), True
    Range("D2").Select
    ActiveWindow.FreezePanes = True
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014

ADVERTISEMENT

EDIT: Actually, even that doesn't work. How can I make it work?
-NBVC

Only way that should fail would be if panes were already frozen. Just set to FALSE then to TRUE to make sure. And FYI, should you ever need to FreezePanes with a different cell in the top left corner, use application.goto Reference:=Range("B3"), Scroll:=True

<sup>edit</sup> Ah, I see you found GOTO on your own. :biggrin: <sub>/edit</sub>
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
...
<sup>edit</sup> Ah, I see you found GOTO on your own. :biggrin: <sub>/edit</sub>

Where there is a will there is a way....so I had to find it somehow :wink:
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
The other should have worked for you. Unless Application.ScreenUpdating has been set to False.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
The other should have worked for you. Unless Application.ScreenUpdating has been set to False.

Thanks Greg....

That is most likely it then. I do have that setting at the beginning of my code.... there are lots of things happening, so i set screenupdating to false to avoid the flickering and such...

at least I know i was on the right track :)
 

Forum statistics

Threads
1,136,712
Messages
5,677,335
Members
419,688
Latest member
sarahmichelle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top