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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
...
<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:
 
Upvote 0
The other should have worked for you. Unless Application.ScreenUpdating has been set to False.
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,217,440
Messages
6,136,636
Members
450,022
Latest member
Joel1122331

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
Back
Top