EnableSelection = xlUnlockedCells

[Stuart]

If I say:
With ws
.Protect
.EnableSelection = xlUnlockedCells
End With
and then save the workbook, then when I reopen
it, then it is protected, but the EnableSelection
statement is not operating.

Is this as intended? If so, then will I have to use the
Workbook Open event to set the Selection?

Regards.

 

[Ron de Bruin]

Example for all sheets
See the protection line


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

 

[Stuart]

Many thanks.
I wasn't sure if it was saved with the file, or not.

Regards.

 

[Tom Ogilvy]

http://support.microsoft.com/default.aspx?scid=kb;en-us;169106&Product=xlw
XL97: Some Worksheet Properties not Preserved

 

[Stuart]

Thanks for the link, will explore.

I didn't use Ron's suggestion, because of the macro warning.
Since the file open was occurring under an addin, I incorporated
the required protection code into the 'open' routine.

Of course the user can always open the file using Excel's File
Open method, in which case my additional protection will not
operate.

I do not wish to disable Excel's File Open, but is there a way
to 'route' it to the addin?

There are 3 addins installed, each storing files under the same
central directory. So if it were possible to 'route' to the addin,
then maybe I could say:
If the path of the file to be opened is C:\AddinSave\ Then
open via addin code
Else
open via Excel File Open method

Just a thought, but is something like this possible, please?

Regards and thanks.

 

[Sunryzz]

Oh Good Grief!!!! Thank you so much. I've been looking for how to do this
all day. I've tried all different kinds of code and nothing worked. I'm
so glad I found this site and your answer!!

Thanks again.