Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Still can select locked cells in protected sheet
When I protect a sheet, and the only action allowed is "Select
unlocked cells", the user cannot select any locked cells, which is what I want. When I save the sheet, and then reopen it, the user can select locked cells. It doesn't do him any good, since he can't alter the cell. If he tries, he gets an error message advising him that the cell is protected. If I unprotect and re-protect the sheet, the behavior goes back to not even being able to select the locked cells. Is there a way to ensure that behavior when the locked worksheet is re-opened? TIA, Andy |
#2
|
|||
|
|||
Still can select locked cells in protected sheet
Andy, I'm afraid that the way you worded it, I (for one) am not clear on
exactly what you want to accomplish. You want the user to be able to select cells but not change them? Or not to even select the cells? And what version are you using, please? ************ Anne Troy www.OfficeArticles.com "Andy" wrote in message ... When I protect a sheet, and the only action allowed is "Select unlocked cells", the user cannot select any locked cells, which is what I want. When I save the sheet, and then reopen it, the user can select locked cells. It doesn't do him any good, since he can't alter the cell. If he tries, he gets an error message advising him that the cell is protected. If I unprotect and re-protect the sheet, the behavior goes back to not even being able to select the locked cells. Is there a way to ensure that behavior when the locked worksheet is re-opened? TIA, Andy |
#3
|
|||
|
|||
Still can select locked cells in protected sheet
Hi andy
If the user is using Excel 2000 this is not working This was added in 2002 -- Regards Ron de Bruin http://www.rondebruin.nl "Andy" wrote in message ... When I protect a sheet, and the only action allowed is "Select unlocked cells", the user cannot select any locked cells, which is what I want. When I save the sheet, and then reopen it, the user can select locked cells. It doesn't do him any good, since he can't alter the cell. If he tries, he gets an error message advising him that the cell is protected. If I unprotect and re-protect the sheet, the behavior goes back to not even being able to select the locked cells. Is there a way to ensure that behavior when the locked worksheet is re-opened? TIA, Andy |
#4
|
|||
|
|||
Still can select locked cells in protected sheet
Anne,
I'm using Excel 2002, and the following macro does what *I* want, which is to not even be able to select the locked cells: Sub Workbook_open() 'put this in This Workbook Dim sh As Worksheet For Each sh In ActiveWorkbook.Worksheets sh.Protect sh.EnableSelection = xlUnlockedCells Next sh End Sub Without the macro, since I saved the workbook with all the sheets protected, when I reopen the file I still can't alter the locked cells, but I can select locked cells. It's not that big a deal but it's aesthetically less pleasing, since I can click on a protected cell and see the heavy active cell border, and trigger the error message if I try to enter something in the protected cell. None of this behavior is possible if I re-protect the sheets. I'm fine with my fix above. The problem is that I want to share the template, and I assume the Workbook_open macro will trigger other user's macro security warnings. Andy |
#5
|
|||
|
|||
Still can select locked cells in protected sheet
Ron,
See my reply to Anne. I think one of the users I want to share the workbook with is using an older version of Excel than either 2002 or 2000. I'll be on the lookout for problems... Andy PS I can't believe a bare TEN MINUTES after I pose my question I have two responses. I love you guys!!! |
#6
|
|||
|
|||
Still can select locked cells in protected sheet
Ron,
What is it that doesn't work in Excel 2000 (I have 2002), that cells I lock can be changed by a 2000 user even though I protected the worksheet? Andy Hi andy If the user is using Excel 2000 this is not working This was added in 2002 -- Regards Ron de Bruin http://www.rondebruin.nl "Andy" wrote in message ... When I protect a sheet, and the only action allowed is "Select unlocked cells", the user cannot select any locked cells, which is what I want. When I save the sheet, and then reopen it, the user can select locked cells. It doesn't do him any good, since he can't alter the cell. If he tries, he gets an error message advising him that the cell is protected. If I unprotect and re-protect the sheet, the behavior goes back to not even being able to select the locked cells. Is there a way to ensure that behavior when the locked worksheet is re-opened? TIA, Andy |
#7
|
|||
|
|||
Still can select locked cells in protected sheet
Hate to ask, Andy, but are you sure you're locking the cells properly?
See: http://www.officearticles.com/excel/...soft_excel.htm ************ Anne Troy www.OfficeArticles.com "Andy" wrote in message ... Ron, What is it that doesn't work in Excel 2000 (I have 2002), that cells I lock can be changed by a 2000 user even though I protected the worksheet? Andy Hi andy If the user is using Excel 2000 this is not working This was added in 2002 -- Regards Ron de Bruin http://www.rondebruin.nl "Andy" wrote in message ... When I protect a sheet, and the only action allowed is "Select unlocked cells", the user cannot select any locked cells, which is what I want. When I save the sheet, and then reopen it, the user can select locked cells. It doesn't do him any good, since he can't alter the cell. If he tries, he gets an error message advising him that the cell is protected. If I unprotect and re-protect the sheet, the behavior goes back to not even being able to select the locked cells. Is there a way to ensure that behavior when the locked worksheet is re-opened? TIA, Andy |
#8
|
|||
|
|||
Still can select locked cells in protected sheet
Hi Andy
The option to do this when you manual protect your sheet is not in 97-2000. So if you manual protect your sheet with this setting in 2002 and open it in 2000 it is not working -- Regards Ron de Bruin http://www.rondebruin.nl "Andy" wrote in message ... Ron, What is it that doesn't work in Excel 2000 (I have 2002), that cells I lock can be changed by a 2000 user even though I protected the worksheet? Andy Hi andy If the user is using Excel 2000 this is not working This was added in 2002 -- Regards Ron de Bruin http://www.rondebruin.nl "Andy" wrote in message ... When I protect a sheet, and the only action allowed is "Select unlocked cells", the user cannot select any locked cells, which is what I want. When I save the sheet, and then reopen it, the user can select locked cells. It doesn't do him any good, since he can't alter the cell. If he tries, he gets an error message advising him that the cell is protected. If I unprotect and re-protect the sheet, the behavior goes back to not even being able to select the locked cells. Is there a way to ensure that behavior when the locked worksheet is re-opened? TIA, Andy |
#9
|
|||
|
|||
Still can select locked cells in protected sheet
are you sure you're locking the cells properly?
I think so Anne. On the Protection tab of the Format Cells dialog box I have "Locked" checked on all of the cells I don't want the user to be able to access. Then I go to ToolsProtectionProtectSheet and the only box checked on the Protect Sheet dialog box is "Select unlocked cells". When I do this (Excel 2002), everything works as expected. But when I save and reopen the file, I can select the locked cells with the mouse. I can't do anything with the selected cells, so the sheet is still protected and only the unlocked cells are truly available. But I wonder why the behavior changes when I reopen the file. Andy |
#10
|
|||
|
|||
Still can select locked cells in protected sheet
Andy: When I test this in 2003, I cannot select cells.
When I test in 2000, I can still select cells. Are you sure you're using the same version always? I have 4 versions on my PC. Perhaps Excel 2000 is your default, and you're double-clicking? Gotta ask... This behavior is just bizarre. Perhaps you should try troubleshooting Excel? http://www.officearticles.com/excel/...soft_excel.htm ************ Anne Troy www.OfficeArticles.com "Andy" wrote in message ... are you sure you're locking the cells properly? I think so Anne. On the Protection tab of the Format Cells dialog box I have "Locked" checked on all of the cells I don't want the user to be able to access. Then I go to ToolsProtectionProtectSheet and the only box checked on the Protect Sheet dialog box is "Select unlocked cells". When I do this (Excel 2002), everything works as expected. But when I save and reopen the file, I can select the locked cells with the mouse. I can't do anything with the selected cells, so the sheet is still protected and only the unlocked cells are truly available. But I wonder why the behavior changes when I reopen the file. Andy |
#11
|
|||
|
|||
Still can select locked cells in protected sheet
The option to do this when you manual protect your sheet is not in
97-2000. So if you manual protect your sheet with this setting in 2002 and open it in 2000 it is not working Hi Ron, Ok, if I understand you right, in 97-2000 I can protect the cell but when the sheet is locked the user will still be able to select the cell even though they won't be able to alter it. Still doesn't explain why my 2002 is allowing me to select the cell, but I'm ok with my workbook_open workaround. Thanks, Andy |
#12
|
|||
|
|||
Still can select locked cells in protected sheet
Hi Anne,
I only have 2002 on my machine, so I guess the behavior is as you say bizarre. I'm ok with my workbook_open workaround, and unless something else becomes screwy, I'm going to put off the troubleshooting project (but I'm keeping the link!). Thanks, Andy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cells User Select Locked after upgrade to Excel 2002 | Excel Discussion (Misc queries) | |||
requires that merged cells must be identically sized? | Excel Discussion (Misc queries) | |||
Sorting on a protected worksheet | Excel Discussion (Misc queries) | |||
Entering Data in multiple cells on one sheet & having it auto upda | Excel Worksheet Functions | |||
how can i select all the cells with same color on a sheet if there are multipale colors by vba code | Charts and Charting in Excel |