Freeze/Unfreeze without activating

[opislak]

Is it necessary to activate a sheet when one wants to freeze or unfreeze panes in vba? Can't this be avoided?
I want to visualize all sheets and freeze them on B5.

Private Sub ShowSheets()
    For Each ws In ThisWorkbook.Worksheets
        With ws
            .Visible = True
            .Activate
        End With
        Range("B5").Select
        ActiveWindow.FreezePanes = True
    Next ws
End Sub

 

[Joe4]

Yes, I believe it is necessary to activate each sheet in order to freeze panes.

If you do not want to use VBA, perhaps you could download the KuTools add-in, and use what they built.
See : How to apply freeze / unfreeze panes to multiple worksheets at once?

 

[opislak]

Yes, I believe it is necessary to activate each sheet in order to freeze panes.

If you do not want to use VBA, perhaps you could download the KuTools add-in, and use what they built.
See : How to apply freeze / unfreeze panes to multiple worksheets at once?

Thank you Joe4.
I asked this because I read somewhere that it is better to avoid using Select and Activate.
But OK, in this case I will use it.

 

[Joe4]

I asked this because I read somewhere that it is better to avoid using Select and Activate.
But OK, in this case I will use it.

That is true, whenever possible to avoid using them. But sometimes, it is not possible!
Since I think you can only use the FreezePanes with "ActiveWindow", I don't think there is any way of getting around it in this case.

If you only have a handful of sheets, it is not that big a deal, as you would only be doing a handful of selects.
But if you were looping through a range of thousands of cells, and were doing a select in each one, that would be thousands of selects, which would definitely slow down your code.