xlUnlocked Cells Not Working

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

I have a file where sheet 1 is protected but has two unlocked cells. I have
set in VBA browser the EnableSelection to xlUnlockedCells.

Returning to the spreadsheet, sheet 1 correctly only allows movement between
the two unlocked cells. However, if I close the file after saving, when
opened the xlUnlockedCells has been turned off.

Any ideas?

Thanks, Robert
 
Hi

This setting is not saved with the file
You must add code to the workbook open event to set it
every time you open the workbook

If you copy this event in the Thisworkbook module
http://www.mcgimpsey.com/excel/modules.html

It will run automatic when you open the workbook and
Protect each sheet in the workbook and set the EnableSelection

Test it on a test workbook

Private Sub Workbook_Open()
Dim Sh As Worksheet
Application.ScreenUpdating = False
For Each Sh In ThisWorkbook.Worksheets
Sh.Select
Sh.Protect userinterfaceonly:=True
Sh.EnableSelection = xlUnlockedCells
Next
Sheets(1).Select
Application.ScreenUpdating = True
End Sub
 
Thanks Ron for pointing me in the right direction. Will test out the sample
code.

Regards, Robert
 
Hi Robert

Do you know that you have this option in Excel 2002-2003
when you protect a sheet?
Tools>Protection....Protect sheet
 
Ron,

I'm, using 2000 and the sheet protection as you suggest. I've played with
the code and initially get the Object 91 error, however, I now refer to the
only sheet that I want to set xlUnlockedCells by Sheets
("Main").EnableSelection = xlUnlockedCells.

I guess the 91 error is to do with not being on the sheet that I need to
make the changes to.

Thanks, Robert
 
Back
Top