MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

help with freezing panes so labels scroll with the page macro

  Asked By: Mona    Date: Oct 03    Category: MS Office    Views: 1283

I keep getting a runtime 1004 error "unable to set FreezePanes of
the Window class" in this macro, AFTER they had been previously
set. I tried using activesheet, sh.FreezePanes = False,
Sheet.FreezePanes = False, and everything else I could think of, but
nothing seems to work. What am I doing wrong here? Or, since the
goal is simply to have the first row locked, so when the user
scrolls down, all the heading/labels follow them, anyone have any
other ideas? The code is:

Sub frezpanes()
Dim sh As Object

Application.EnableEvents = False

For Each sh In Sheets
If ActiveWindow.FreezePanes = False Then
'do nothing
ActiveWindow.FreezePanes = False
'<--error shows here
End If

If sh.Name = "Info" Then
'do nothing
ElseIf sh.Name = "Namelist" Then
'do nothing
ElseIf sh.Name = "Total Stats" Then
ActiveWindow.FreezePanes = True
ActiveWindow.FreezePanes = True
'<--error showed here2
End If

Next sh
Application.EnableEvents = False
end sub

The first "if statement" was an attempt to correct the first error,
which is located where "'<--error showed here2" is in the code. My
thought was that the problem was that if the FreezePanes was already
set to "True",that it was firing the error. Apparently that's not
the case, so I'm a bit lost about what to correct.



3 Answers Found

Answer #1    Answered By: Zane Thompson     Answered On: Oct 03

Oh, and I forgot to mention that the reason they may need to be
reset is that the data sometimes totally changes, so some pages get
erased and the new date inserted. I had thought about just leaving
them set all the time, but haven't been able to figure out how to
reset all the data if they were.

It wouldn't be the first time that I misses something obvious,

Answer #2    Answered By: Constance Reid     Answered On: Oct 03

I had to update the screen before I used FreezePanes.

My code snippet is:

' !!NOTE: Screen has to be updated to place
' the freeze in the correct location!
' restore screen updates and postion screen to top left of data
Application.ScreenUpdating = True
ActiveWindow.FreezePanes = True

Answer #3    Answered By: Este Ferrrari     Answered On: Oct 03

I happen to enjoy the cold weather, but my wife thinks I'm crazy
about that, so my view may not be all that significant. Stay tuned.)