Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fix Excel Protection so that it works as published
When setting Excel Protection, the options include allowing sorting, allowing
deleting of rows, etc. In my experience with Excel 2003, these options do NOT work as advertised: even though I have ticked those options Excel still forces me to remove protection before sorting protected cells or deleting rows in a protected sheet. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fix Excel Protection so that it works as published
According to the MS Excel help text entitled "Overview of security and
protection in Excel" if on the Protect panel I check the Sort checkbox then users are supposed to be able to sort data on the worksheet. If I can only sort unlocked cells, then the feature is out of step with the documentation; it's also next to useless, since it means that on a form where rows contain both locked and unlocked cells I am not able to allow users to sort anything (since that would involve sorting locked cells). I stand by my original statement: MS needs to fix this feature so that it works like the documentation says it works. Thanks for trying to help. Regards, Christopher "Peo Sjoblom" wrote: It works, you have to unlock the cells in question before you protect it under formatcellsprotection, select the ranges you want to sort and uncheck locked, then protect the sheet with these options -- Regards, Peo Sjoblom Portland, Oregon "Christopher Rath" <Christopher wrote in message ... When setting Excel Protection, the options include allowing sorting, allowing deleting of rows, etc. In my experience with Excel 2003, these options do NOT work as advertised: even though I have ticked those options Excel still forces me to remove protection before sorting protected cells or deleting rows in a protected sheet. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fix Excel Protection so that it works as published
Of fix the documentation so there are no misunderstandings, on the other
hand if I look in help I see this "Sort When cleared, prevents users from using any of the Sort commands on the Data menu, or the Sort buttons on the Standard toolbar. Users can't sort ranges containing locked cells on a protected worksheet, regardless of this setting." Which to me is rather obvious -- Regards, Peo Sjoblom Portland, Oregon "Christopher Rath" wrote in message ... According to the MS Excel help text entitled "Overview of security and protection in Excel" if on the Protect panel I check the Sort checkbox then users are supposed to be able to sort data on the worksheet. If I can only sort unlocked cells, then the feature is out of step with the documentation; it's also next to useless, since it means that on a form where rows contain both locked and unlocked cells I am not able to allow users to sort anything (since that would involve sorting locked cells). I stand by my original statement: MS needs to fix this feature so that it works like the documentation says it works. Thanks for trying to help. Regards, Christopher "Peo Sjoblom" wrote: It works, you have to unlock the cells in question before you protect it under formatcellsprotection, select the ranges you want to sort and uncheck locked, then protect the sheet with these options -- Regards, Peo Sjoblom Portland, Oregon "Christopher Rath" <Christopher wrote in message ... When setting Excel Protection, the options include allowing sorting, allowing deleting of rows, etc. In my experience with Excel 2003, these options do NOT work as advertised: even though I have ticked those options Excel still forces me to remove protection before sorting protected cells or deleting rows in a protected sheet. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fix Excel Protection so that it works as published
Forgive my oversight; I missed that statement when I reviewed the help
earlier. MS should add that same caveat to the "Delete rows" help text too. I had been attempting to protect the formalae in some cells from accidental deletion. Now I shall have to tell users to simply avoid making mistakes (and deleting those cell contents by accident); since protecting them removes other necessary functions. Christopher "Peo Sjoblom" wrote: Of fix the documentation so there are no misunderstandings, on the other hand if I look in help I see this "Sort When cleared, prevents users from using any of the Sort commands on the Data menu, or the Sort buttons on the Standard toolbar. Users can't sort ranges containing locked cells on a protected worksheet, regardless of this setting." Which to me is rather obvious -- Regards, Peo Sjoblom Portland, Oregon "Christopher Rath" wrote in message ... According to the MS Excel help text entitled "Overview of security and protection in Excel" if on the Protect panel I check the Sort checkbox then users are supposed to be able to sort data on the worksheet. If I can only sort unlocked cells, then the feature is out of step with the documentation; it's also next to useless, since it means that on a form where rows contain both locked and unlocked cells I am not able to allow users to sort anything (since that would involve sorting locked cells). I stand by my original statement: MS needs to fix this feature so that it works like the documentation says it works. Thanks for trying to help. Regards, Christopher "Peo Sjoblom" wrote: It works, you have to unlock the cells in question before you protect it under formatcellsprotection, select the ranges you want to sort and uncheck locked, then protect the sheet with these options -- Regards, Peo Sjoblom Portland, Oregon "Christopher Rath" <Christopher wrote in message ... When setting Excel Protection, the options include allowing sorting, allowing deleting of rows, etc. In my experience with Excel 2003, these options do NOT work as advertised: even though I have ticked those options Excel still forces me to remove protection before sorting protected cells or deleting rows in a protected sheet. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fix Excel Protection so that it works as published
I agree that help could be much better in fact I think help has degraded
with each version of Office/Excel and you are right that help does not mention this when it comes to rows/columns. -- Regards, Peo Sjoblom Portland, Oregon "Christopher Rath" wrote in message ... Forgive my oversight; I missed that statement when I reviewed the help earlier. MS should add that same caveat to the "Delete rows" help text too. I had been attempting to protect the formalae in some cells from accidental deletion. Now I shall have to tell users to simply avoid making mistakes (and deleting those cell contents by accident); since protecting them removes other necessary functions. Christopher "Peo Sjoblom" wrote: Of fix the documentation so there are no misunderstandings, on the other hand if I look in help I see this "Sort When cleared, prevents users from using any of the Sort commands on the Data menu, or the Sort buttons on the Standard toolbar. Users can't sort ranges containing locked cells on a protected worksheet, regardless of this setting." Which to me is rather obvious -- Regards, Peo Sjoblom Portland, Oregon "Christopher Rath" wrote in message ... According to the MS Excel help text entitled "Overview of security and protection in Excel" if on the Protect panel I check the Sort checkbox then users are supposed to be able to sort data on the worksheet. If I can only sort unlocked cells, then the feature is out of step with the documentation; it's also next to useless, since it means that on a form where rows contain both locked and unlocked cells I am not able to allow users to sort anything (since that would involve sorting locked cells). I stand by my original statement: MS needs to fix this feature so that it works like the documentation says it works. Thanks for trying to help. Regards, Christopher "Peo Sjoblom" wrote: It works, you have to unlock the cells in question before you protect it under formatcellsprotection, select the ranges you want to sort and uncheck locked, then protect the sheet with these options -- Regards, Peo Sjoblom Portland, Oregon "Christopher Rath" <Christopher wrote in message ... When setting Excel Protection, the options include allowing sorting, allowing deleting of rows, etc. In my experience with Excel 2003, these options do NOT work as advertised: even though I have ticked those options Excel still forces me to remove protection before sorting protected cells or deleting rows in a protected sheet. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fix Excel Protection so that it works as published
Hi Christopher I'm trying to overcome exactly the same problem (sorting a protected sheet) at the moment & am just checking threads to see if there is a method of adding default arguments to the following "SortSheet" code which I have attached to a commanbutton. Please note that I have set the sheet up with "userinterface only" protection using: Private Sub Workbook_Open() Dim Password As String Password = "secret" Sheets("sheet1").Protect Password:=Password, _ UserInterFaceOnly:=True End Sub Add a named range "sort_area", give the below a go & see if it helps - I think it is much better than asking users not to make errors, after all we are all human! Sub SortSheet() 'to allow sheet sorting on the "user interface only" protected sheet & requires use of a _ named range "Sort_Area" Range("'" & ActiveWorkbook.Name & "'!Sort_Area").Select Application.Dialogs(xlDialogSortSpecial).Show 'optional MsgBox "Page is sorted as requested." End Sub hth, Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=501367 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fix Excel Protection so that it works as published
I have the same problem. Many of my users are inexperienced with excel.
Having them unprotect, then sort, then re-protect is asking for trouble. I also have protected columns and unprotected columns. I would like to be able to sort across all the columns and be able to clear the unprotected contents of a block containintgboth protected and unprotected data. "Christopher Rath" wrote: When setting Excel Protection, the options include allowing sorting, allowing deleting of rows, etc. In my experience with Excel 2003, these options do NOT work as advertised: even though I have ticked those options Excel still forces me to remove protection before sorting protected cells or deleting rows in a protected sheet. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fix Excel Protection so that it works as published
Your solution is way beyond my pay grade.
"broro183" wrote: Hi Christopher I'm trying to overcome exactly the same problem (sorting a protected sheet) at the moment & am just checking threads to see if there is a method of adding default arguments to the following "SortSheet" code which I have attached to a commanbutton. Please note that I have set the sheet up with "userinterface only" protection using: Private Sub Workbook_Open() Dim Password As String Password = "secret" Sheets("sheet1").Protect Password:=Password, _ UserInterFaceOnly:=True End Sub Add a named range "sort_area", give the below a go & see if it helps - I think it is much better than asking users not to make errors, after all we are all human! Sub SortSheet() 'to allow sheet sorting on the "user interface only" protected sheet & requires use of a _ named range "Sort_Area" Range("'" & ActiveWorkbook.Name & "'!Sort_Area").Select Application.Dialogs(xlDialogSortSpecial).Show 'optional MsgBox "Page is sorted as requested." End Sub hth, Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=501367 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
unhide menu bar in excel - just disappeared | Setting up and Configuration of Excel | |||
#VALUE! error: vlookup works in Excel 2000 but not 2003 | Excel Discussion (Misc queries) | |||
saving excel lists in works | Setting up and Configuration of Excel | |||
Opening Office Excel file in Works suite | Excel Discussion (Misc queries) |