Excel - ActiveWindow.FreezePanes 1

[Andrzejek]

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:

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:

With Sheets(5)[red]
    .Activate[/red]
    .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

 

[sxschech]

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

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
            '[URL unfurl="true"]http://www.utteraccess.com/forum/lofiversion/index.php/t1751716.html[/URL]
            .Application.ActiveWindow.splitrow = 1
            .Application.ActiveWindow.freezepanes = True

 

[Andrzejek]

I was so close, all what I had missing was:

With Sheets(5)[green]
    '.[s]Activate[/s][/green]
    .Range("A2").Select
    [highlight #FCE94F].Application[/highlight].ActiveWindow.FreezePanes = True
End With

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

---- Andy

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

 

[sxschech]

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.