Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent selecting locked cells
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent selecting locked cells
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 ** "Andy" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent selecting locked cells
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. "Paul B" wrote: 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 ** "Andy" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent selecting locked cells
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" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent selecting locked cells
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" wrote: 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" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent selecting locked cells
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" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent selecting locked cells
Thanks very much Tom, that makes sense.
"Tom Ogilvy" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cursor selecting cells locked on... | Excel Discussion (Misc queries) | |||
Prevent Users from Selecting Cells | Excel Worksheet Functions | |||
how do I prevent users to go into locked cells | Excel Discussion (Misc queries) | |||
how do I prevent users to go into locked cells | Excel Discussion (Misc queries) | |||
Selecting only locked cells | Excel Programming |