Prevent selecting locked cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to be able to prevent users from selecting locked cells in a
worksheet and to do this have used the EnableSelection property with the
Protect method. The problem I had was that when the workbook is closed and
re-opened the locked cells are still selectable. Having looked at previous
posts I see that this is because the EnableSelection property is not
persistent. However, it does seem to be persistent if I manually protect the
worksheet so that only unlocked cells can be selected. How come this can't be
achieved with VBA?
 
Andy, this is a setting that xl doesn't remember between closings,so have
your auto_open or workbook_open code do it each time, like this

Private Sub Workbook_Open()
'will not let you select locked cells
'change to your worksheet name

With Worksheets("sheet1")
..Activate
..EnableSelection = xlUnlockedCells
'change to your passwrd
'commet out if you don't want a password
..Protect "123"
..Protect Contents:=True, UserInterfaceOnly:=True
End With
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 2003
** remove news from my email address to reply by email **
 
Thanks Paul, however, I still don't understand why Excel remembers the
setting if it is done manually and not if it is done by code...
Also, if possible I would like to avoid having a macro attached to the
workbook itself becasue the code I have written runs from Access and
pre-fills some questions in a questionnaire designed in Excel. The
questionnaire is then emailed to participants to complete and I believe there
can be problems with firewalls and filters blocking spreadsheets that contain
macros.
 
Just to add:
To the best of my knowledge, at least in xl97 and xl2000, the
enableselection setting is not persistent if set manually or with code. In
Excel 2002/3 there are options for this in the enhanced sheet protection
dialog. I don't have xl2003 installed, but I found the behavior you
describe in xl2002. If the sheet is protected manually and the choices made
in that dialog (either previously by code [retain the settings displayed] or
by hand [make a change or not]) and the workbook is saved, then the setting
is retained. If the sheet is not protected, then the settings are not
retained. Making a selection via code and protecting the workbook - the
settings are not retained. This refers to after closing and then
reopening the workbook.
 
Thanks Tom, so the EnableSelection property is only persistent if the sheet
is protected manually rather than by code? I still don't understand why
there is a difference between doing it manually and through code. By using
VBA code shouldn't the end result be the same as clicking toolbar buttons and
dialogs?! I've even tried recording a macro as I do the changes manually and
the code it records is the same as I have written. I guess if this is
defintiely the case, my only option is to leave it to the user to manually
protect each worksheet once the rest of the code has run...


Tom Ogilvy said:
Just to add:
To the best of my knowledge, at least in xl97 and xl2000, the
enableselection setting is not persistent if set manually or with code. In
Excel 2002/3 there are options for this in the enhanced sheet protection
dialog. I don't have xl2003 installed, but I found the behavior you
describe in xl2002. If the sheet is protected manually and the choices made
in that dialog (either previously by code [retain the settings displayed] or
by hand [make a change or not]) and the workbook is saved, then the setting
is retained. If the sheet is not protected, then the settings are not
retained. Making a selection via code and protecting the workbook - the
settings are not retained. This refers to after closing and then
reopening the workbook.

--
Regards,
Tom Ogilvy

Andy said:
I would like to be able to prevent users from selecting locked cells in a
worksheet and to do this have used the EnableSelection property with the
Protect method. The problem I had was that when the workbook is closed and
re-opened the locked cells are still selectable. Having looked at previous
posts I see that this is because the EnableSelection property is not
persistent. However, it does seem to be persistent if I manually protect the
worksheet so that only unlocked cells can be selected. How come this can't be
achieved with VBA?
Click to expand...
Click to expand...
 
I suspect it is a bug.

I assume somewhere, when you do it manually, excel records the settings in a
table or structure. It uses this structure to implement the settings, one
of which is the enableselection property. However, when you set
enableselection with code, you do it directly and my assumption is this
setting is not recorded or migrated back to the table or structure. This is
totally a surmise on my part - but the majority of options in the protect
dialog are set using the protect method in VBA - enableselection is not
however. For consistency, it uses the orignal method provided in
xl97/xl2000. This is where I think the disconnect occurs as I have surmised
above.

--
Regards,
Tom Ogilvy

Andy said:
Thanks Tom, so the EnableSelection property is only persistent if the sheet
is protected manually rather than by code? I still don't understand why
there is a difference between doing it manually and through code. By using
VBA code shouldn't the end result be the same as clicking toolbar buttons and
dialogs?! I've even tried recording a macro as I do the changes manually and
the code it records is the same as I have written. I guess if this is
defintiely the case, my only option is to leave it to the user to manually
protect each worksheet once the rest of the code has run...


Tom Ogilvy said:
Just to add:
To the best of my knowledge, at least in xl97 and xl2000, the
enableselection setting is not persistent if set manually or with code. In
Excel 2002/3 there are options for this in the enhanced sheet protection
dialog. I don't have xl2003 installed, but I found the behavior you
describe in xl2002. If the sheet is protected manually and the choices made
in that dialog (either previously by code [retain the settings displayed] or
by hand [make a change or not]) and the workbook is saved, then the setting
is retained. If the sheet is not protected, then the settings are not
retained. Making a selection via code and protecting the workbook - the
settings are not retained. This refers to after closing and then
reopening the workbook.

--
Regards,
Tom Ogilvy

Andy said:
I would like to be able to prevent users from selecting locked cells in a
worksheet and to do this have used the EnableSelection property with the
Protect method. The problem I had was that when the workbook is
Click to expand...
closed
and
re-opened the locked cells are still selectable. Having looked at previous
posts I see that this is because the EnableSelection property is not
persistent. However, it does seem to be persistent if I manually
Click to expand...
protect
the
worksheet so that only unlocked cells can be selected. How come this
Click to expand...
can't
be
achieved with VBA?
Click to expand...
Click to expand...
Click to expand...
 
Thanks very much Tom, that makes sense.

Tom Ogilvy said:
I suspect it is a bug.

I assume somewhere, when you do it manually, excel records the settings in a
table or structure. It uses this structure to implement the settings, one
of which is the enableselection property. However, when you set
enableselection with code, you do it directly and my assumption is this
setting is not recorded or migrated back to the table or structure. This is
totally a surmise on my part - but the majority of options in the protect
dialog are set using the protect method in VBA - enableselection is not
however. For consistency, it uses the orignal method provided in
xl97/xl2000. This is where I think the disconnect occurs as I have surmised
above.

--
Regards,
Tom Ogilvy

Andy said:
Thanks Tom, so the EnableSelection property is only persistent if the sheet
is protected manually rather than by code? I still don't understand why
there is a difference between doing it manually and through code. By using
VBA code shouldn't the end result be the same as clicking toolbar buttons and
dialogs?! I've even tried recording a macro as I do the changes manually and
the code it records is the same as I have written. I guess if this is
defintiely the case, my only option is to leave it to the user to manually
protect each worksheet once the rest of the code has run...


Tom Ogilvy said:
Just to add:
To the best of my knowledge, at least in xl97 and xl2000, the
enableselection setting is not persistent if set manually or with code. In
Excel 2002/3 there are options for this in the enhanced sheet protection
dialog. I don't have xl2003 installed, but I found the behavior you
describe in xl2002. If the sheet is protected manually and the choices made
in that dialog (either previously by code [retain the settings displayed] or
by hand [make a change or not]) and the workbook is saved, then the setting
is retained. If the sheet is not protected, then the settings are not
retained. Making a selection via code and protecting the workbook - the
settings are not retained. This refers to after closing and then
reopening the workbook.

--
Regards,
Tom Ogilvy

I would like to be able to prevent users from selecting locked cells in a
worksheet and to do this have used the EnableSelection property with the
Protect method. The problem I had was that when the workbook is closed
and
re-opened the locked cells are still selectable. Having looked at
previous
posts I see that this is because the EnableSelection property is not
persistent. However, it does seem to be persistent if I manually protect
the
worksheet so that only unlocked cells can be selected. How come this can't
be
achieved with VBA?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top