Freeze/Unfreeze without activating

opislak

Board Regular
Joined
Feb 28, 2017
Messages
61
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.

VBA Code:
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

opislak

Board Regular
Joined
Feb 28, 2017
Messages
61

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,388
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,137,156
Messages
5,679,924
Members
419,862
Latest member
Bluewings666

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top