×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel - ActiveWindow.FreezePanes

Excel - ActiveWindow.FreezePanes

Excel - ActiveWindow.FreezePanes

(OP)
I populate an Excel file with the data from my data base, and often I would like to Freeze Panes so the header will be always visible, even if user scrolls up or down.

What I do is:

CODE

With Sheets(1)
    .Range("A2").Select
    .ActiveWindow.FreezePanes = True
End With 

That works fine if Sheet(1) is an Active sheet.
But if I place data in Sheet(5) without 'activating' that worksheet, I get an error: 'Object doesn't support this property or method.' Makes sense.

But that happens even if I do Activate this sheet:

CODE

With Sheets(5)
    .Activate
    .Range("A2").Select
    .ActiveWindow.FreezePanes = True
End With 

So, what would be the way to FreezePanes on Sheets(5)?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Excel - ActiveWindow.FreezePanes

Here is a snippet of what I use from within MS-Access, fairly close to what you provided.

CODE -->

For Each WS In wb.Worksheets
        With WS
            'Even though formatting code does not need to be
            'on the active sheet, seems that for freezing panes
            'it does, so go to next sheet with activate
            .Activate
            'http://www.utteraccess.com/forum/lofiversion/index.php/t1751716.html
            .Application.ActiveWindow.splitrow = 1
            .Application.ActiveWindow.freezepanes = True 

RE: Excel - ActiveWindow.FreezePanes

(OP)
I was so close, all what I had missing was:

CODE

With Sheets(5)
    '.Activate
    .Range("A2").Select
    .Application.ActiveWindow.FreezePanes = True
End With 

And, turns out, I don't even need Activate thumbsup2

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Excel - ActiveWindow.FreezePanes

Glad you got it working. Sometimes it seems to come down to missing a keyword or line 2 has to go before line one kind of thing.

I wonder why before we needed to Activate and now not? Haven't used that code in a while so not sure when I'll have a chance to test out removing the Activate at my end.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close