Unable to lock scroll area or Cell selection

  • Thread starter Thread starter Paul
  • Start date Start date
P

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
 
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 ***
 
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 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.
 
Thanks to the 2 Daves, Dave1's code and Dave2's advice where to stick
it, this works perfectly.

Regards,

Paul
 
Back
Top