The basic principles of this solution can be applied to XlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ĪctiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=TrueĪfter running the macro, you get a sorted worksheet that is We accomplish this through a simple macro: To allow users access to the previous test scores. Sort the table listing the highest grades down to the lowest.īut we can’t sort a protected worksheet. Now that the scores have been entered for Test 5, we want to Is still allowed to enter data for Test5. Note that all the worksheet data is protected, but the user Go to Tools | Protection | Protect Sheet to protect the Navigate to Format Cells in the dialog box. We want to protect all of the previous test scores, but weĪlso want to add test scores for Test5 when the time comes. Power and flexibility provided by the built-in Visual Basic for Applications The most complete solution to this problem involves the Permission to change previously entered data.
Want to give users permission to add data and sort the results, but not May want to give users the ability to sort data without also giving them theĪbility to change actual data values. The problem is, in some circumstances, you That pretty much sums up the situation when it comes to sorting data in a ProblemĪdvocate, I was reminded of the axiom: You can’t always get what you want.
The key to overcoming this limitation lies with a macro and Visual Basic for Applications. Sorting data in a protected worksheet can be accomplished only if the data is first unprotected. Sort and format a protected Excel worksheet with a macro
0 kommentar(er)
