Limiting a worksheet's selection range.

[Ken Johnson]

xl2003, Windows XP:

Sub SaveSelectUnlocked()
Dim SaveAsFilename
ActiveSheet.Unprotect
With Range("A1,B2,C3")
.Locked = False
.FormulaHidden = False
End With
With ActiveSheet
.EnableSelection = xlUnlockedCells
.Protect
End With
ActiveWorkbook.Protect
SaveAsFilename = Application.GetSaveAsFilename(InitialFileName:="")
If SaveAsFilename <> False Then ActiveWorkbook.SaveAs (SaveAsFilename)
End Sub

I was hoping the above code would result in a SavedAs copy of the
workbook with the only selectable cells being A1, B2 and C3. As soon
as the code has finished running that is the case. However, after
closing and reopening the workbook, all cells are selectable. Even
manually saving the workbook resulting from the code, closing and
reopening results in all cells being selectable.

Is there a way of programatically limiting a worksheet's selection
range to unlocked cells so that it is retained between sessions?

Ken Johnson

 

[Barb Reinhardt]

Change your

SaveAsFilename = Application.GetSaveAsFilename(InitialFileName:="", _
FileFilter:="Microsoft Office Excel Workbook (*.xls),*xls")

 

[Ken Johnson]

Change your

SaveAsFilename = Application.GetSaveAsFilename(InitialFileName:="", _
FileFilter:="Microsoft Office Excel Workbook (*.xls),*xls")

Hi Barb,

Thanks for the FileFilter improvement.
I've added it, however, when I run the code, the result is the same.
I have run the code to save the workbook with a new file name and have
saved it to my desktop.
As before, after the code has finished only A1, B2, and C3 are
selectable. I've checked the active sheet's EnableSelection property
and it is xlUnLockedCells, as expected. I then exit Excel then reopen
the same file from my desktop. Now, all cells are selectable because
the worksheet's EnableSelection property has changed to
xlNoRestrictions.

Here's my new code, maybe I haven't properly incorporated your
suggestion...

Sub SaveSelectUnLocked()
Dim SaveAsFilename
ActiveSheet.Unprotect
Cells.Locked = True
With Range("A1,B2,C3")
.Locked = False
.FormulaHidden = False
End With
With ActiveSheet
.EnableSelection = xlUnlockedCells
.Protect
End With
ActiveWorkbook.Protect
SaveAsFilename =
Application.GetSaveAsFilename(InitialFileName:="", _
filefilter:="Microsoft Office Excel Workbook (*.xls),*xls")
If SaveAsFilename <> False Then ActiveWorkbook.SaveAs
(SaveAsFilename)
End Sub

Thanks for your help.

Ken Johnson

 

[Barb Reinhardt]

I used exactly the code I posted in a workbook here and it worked fine. Do
you have it in a separate module?

 

[Ken Johnson]

I used exactly the code I posted in a workbook here and it worked fine. Do
you have it in a separate module?

Hi Barb,

the code is in a standard module in that workbook.

I've tried it again by just pasting the code I last posted into a
standard module of a new workbook (Book 1).
I ran the code from sheet 1 saving it to the desktop as testing.xls.
The cells A1, B2 and C3 are the only selectable cells, as expected.
I then exited Excel, then re-opened testing.xls from the destop.
Locked and unlocked cells on sheet 1 can all be selected, not as
expected.

Do you get the same result?

Ken Johnson

 

[Barb Reinhardt]

It seems that it's related to what you can choose to display (Locked cells,
unlocked cells) when you protect the worksheet. I generally allow a display
of unlocked cells only and that may be why we are seeing a disconnect.

try replacing the PROTECT (for the worksheet) with this. I'm not sure it
will work

.Protect UserInterfaceOnly:=True

 

[Ken Johnson]

It seems that it's related to what you can choose to display (Locked cells,
unlocked cells) when you protect the worksheet. I generally allow a display
of unlocked cells only and that may be why we are seeing a disconnect.

try replacing the PROTECT (for the worksheet) with this. I'm not sure it
will work

.Protect UserInterfaceOnly:=True

Thanks for solving that problem for me Barb.
I must do some reading on that UserInterfaceOnly thing.

Ken Johnson