ScrollArea Property

[confusion123]

Has anyone used the ScrollArea property extensively to limit access to a worksheet?

From my limited use, it seems to be flaky, ie if I set it to C:F and say column A is hidden, I have found I cannot unhide it.

 

[CalcSux78]

I've found, you need to set the ScrollArea = "" before hiding/unhiding rows or columns. Once changes are complete, restore the ScrollArea. If you wrap your code in Application.ScreenUpdating = False / True, you get a smoother effect.

 

[confusion123]

Thanks

 

[confusion123]

For some reason, if I set the scrollarea property to say B:R, when I save, close then re-open the workbook, the scrollarea property is cleared, meaning I can get to column A, when I don't want to.

 

[CalcSux78]

Do you have any WorkBook Save events (etc...)? Are there any other subs that would change your scroll area? Would you be able to post some code segments?

 

[confusion123]

No, there's no code at all, as I've saved the workbook as .xlsx.

I hide column A, then manually go to Sheet1 in the VB Editor, press F4 to bring up the properties window, then set the ScrollArea to B:R.

Save the workbook, exit then open again and the Scrollarea property is empty.

Try it for yourself, I'm using Excel 2013.

To overcome this, I actively created a Worksheet_Activate event with the code below:

Option Explicit

Private Sub Worksheet_Activate()

    Me.ScrollArea = "B:RS"
    
End Sub

 

[CalcSux78]

The scroll area does not persist when excel closes. In order to have the scroll area locked, you'd need to add code.

example:

Private Sub Workbook_Open()
Sheets(1).ScrollArea = "$B:$R"
End Sub

 

[confusion123]

Thanks, thought so.