Unable to lock scroll area or Cell selection

[Paul]

Hi,

I'm trying to lock the scroll area on my worksheet.
In VB I've got:

Private Sub Workbook_Open()

Worksheets("Board").EnableSelection = xlUnlockedCells
Worksheets("Board").ScrollArea = "$A$1:$u$21"

End Sub

In the sheet Properties I input:

EnableSelection 1-xlUnlockedCells
ScrollArea $A$1:$u$21

I save.
I protect the sheet.
I save again.

This works when I go into the sheet, the necessary cells cannot be
selected and the scroll area is locked the way I want. I save and
close, but upon opening the VB code is still there but EnableSelection
and ScrollArea within properties are blank, and I am once again able
to select any cell and scroll wherever I want. Any ideas?

Thanks

Paul

 

[Dave Hawley]

Hi Paul

This is normal (for want of a better word) when the Worksheets
Properties are set. Your Workbook Open Macro should solve this though.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Dave Hawley]

Try changing the Workbook Open macro to

Private Sub Workbook_Open()
With Worksheets("Board")
.Protect Pasasword:="Secret", UserInterFaceOnly:=True
.EnableSelection = xlUnlockedCells
.ScrollArea = "$A$1:$u$21"
End With
End Sub

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Paul]

Thanks for the reply Dave, but the code suggested has the same effect
as the original...(i assume pasasword is a mispelling.

I've done this before successfully on another sheet and the procedure
I've used looks the same, except that the settings just doesn't seem
to stick when I save it....I've also tested this on another new
worksheet and it to exhibits the same behaviour.

Could it be Excel itself?

Regards,

Paul

 

[Dave Peterson]

Dave Hawley put his code in the workbook_open event (under the ThisWorkbook
module).

There are some settings that excel won't remember after you close the workbook.
They have to be set before you rely on them. A nice spot for this type thing is
in the workbook_open event.

If macros are enabled and events aren't disabled, then this code will run each
time the workbook opens.

And it'll set the, er, settings.

 

[Paul]

Thanks to the 2 Daves, Dave1's code and Dave2's advice where to stick
it, this works perfectly.

Regards,

Paul