Workbook sheet code delay

[Colin Hayes]

Hi

I'm using this code (placed as a ThisWorkbook module) to restrict the
scroll area in the five worksheets in my workbook :


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim mysheets As Sheets
Set mysheets = Worksheets(Array(1, 2, 3, 4, 5))
For Each Sheet In mysheets
Sheet.ScrollArea = "A1:O58"
Next
End Sub


Unfortunately , I find it doesn't work immediately the workbook is
opened :

If go to use the first worksheet , it allows scrolling in all directions
without limit. This isn't the idea.....!

If I click onto the second sheet , the restrictions from the code apply.


If I then go back to the first sheet , the restrictions are now in
force.

Is there any way I can get this to work properly and be in force from
when the workbook opens?


Grateful for any help.



Best Wishes


Colin

 

[toppers]

try this in Thisworkbook:


Private Sub Workbook_Open()
Dim mysheets As Sheets
Set mysheets = Worksheets(Array(1, 2, 3,4,5))
For Each Sheet In mysheets
Sheet.ScrollArea = "A1:O58"
Next
End Sub

 

[Dave Peterson]

Try keeping that code in the same module (ThisWorkbook).

But rename it to:
Sub Workbook_Open()

 

[Colin Hayes]

Hi

OK that's fixed it - thanks!

Much appreciated.



Best Wishes


Colin

 

[Colin Hayes]

try this in Thisworkbook:


Private Sub Workbook_Open()
Dim mysheets As Sheets
Set mysheets = Worksheets(Array(1, 2, 3,4,5))
For Each Sheet In mysheets
Sheet.ScrollArea = "A1:O58"
Next
End Sub

Hi

Just as an extra comment on this :

One thing I've noticed with this code is that , once in place , it
inhibits the choice of rows and columns entirely. You just can't select
them.

Is there anyway of avoiding this when scrolling restriction is in place?

Thanks again