Freezing Panes using VBA

[NBVC]

Hi guys,

Another VBA question.....


I am trying to freeze panes at D2 with the following 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?

 

[Oaktree]

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]

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:

' 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]

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:

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

 

[Greg Truby]

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

^edit Ah, I see you found GOTO on your own. _/edit

 

[NBVC]

...
^edit Ah, I see you found GOTO on your own. _/edit

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

 

[Greg Truby]

The other should have worked for you. Unless Application.ScreenUpdating has been set to False.

 

[NBVC]

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