Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Rows on a password protected worksheet
Hello,
I have an MS Excel worksheet (Excel 2002), for which I have password protectection enabled. There are several columns which the users can enter data, they would also like to sort the data (both asc. & desc.), but as you know, sorting functions are disabled when protection is on. I have tried this code, to no avail: ---------------------------------------------------------------------------------------------- Sub SortSheet() ActiveSheet.Unprotect Password:="cfo2006" Range("A14:Q14").Sort Key1:=Range("C14"), Order1:=xlDescending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveSheet.Protect Password:="cfo2006", DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub --------------------------------------------------------------------------------------------- Can you offer any ideas to help me with this problem?? -- Thanks in advance for your assistance! Coach Tony |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Rows on a password protected worksheet
It looks like your range is only one row and you are sorting top to
bottom so when it sorts there is only one variable in the column to sort, try setting the range to cover all the data and then run the code. Sandy CoachTony wrote: Hello, I have an MS Excel worksheet (Excel 2002), for which I have password protectection enabled. There are several columns which the users can enter data, they would also like to sort the data (both asc. & desc.), but as you know, sorting functions are disabled when protection is on. I have tried this code, to no avail: ---------------------------------------------------------------------------------------------- Sub SortSheet() ActiveSheet.Unprotect Password:="cfo2006" Range("A14:Q14").Sort Key1:=Range("C14"), Order1:=xlDescending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveSheet.Protect Password:="cfo2006", DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub --------------------------------------------------------------------------------------------- Can you offer any ideas to help me with this problem?? -- Thanks in advance for your assistance! Coach Tony |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Rows on a password protected worksheet
If you are trying to sort by row change the orientation to
xlLeftToRight. If you are sorting by columns then you need to adjust your range , you only have one row of data in each column to sort. Sandy CoachTony wrote: Hello, I have an MS Excel worksheet (Excel 2002), for which I have password protectection enabled. There are several columns which the users can enter data, they would also like to sort the data (both asc. & desc.), but as you know, sorting functions are disabled when protection is on. I have tried this code, to no avail: ---------------------------------------------------------------------------------------------- Sub SortSheet() ActiveSheet.Unprotect Password:="cfo2006" Range("A14:Q14").Sort Key1:=Range("C14"), Order1:=xlDescending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveSheet.Protect Password:="cfo2006", DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub --------------------------------------------------------------------------------------------- Can you offer any ideas to help me with this problem?? -- Thanks in advance for your assistance! Coach Tony |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Rows on a password protected worksheet
Sandy, YOU ARE AWESOME!
Works like a charm, when you set it up correctly -) Thanks very much for your quick response, I appreciate it! -- Coach Tony "Sandy" wrote: If you are trying to sort by row change the orientation to xlLeftToRight. If you are sorting by columns then you need to adjust your range , you only have one row of data in each column to sort. Sandy CoachTony wrote: Hello, I have an MS Excel worksheet (Excel 2002), for which I have password protectection enabled. There are several columns which the users can enter data, they would also like to sort the data (both asc. & desc.), but as you know, sorting functions are disabled when protection is on. I have tried this code, to no avail: ---------------------------------------------------------------------------------------------- Sub SortSheet() ActiveSheet.Unprotect Password:="cfo2006" Range("A14:Q14").Sort Key1:=Range("C14"), Order1:=xlDescending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveSheet.Protect Password:="cfo2006", DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub --------------------------------------------------------------------------------------------- Can you offer any ideas to help me with this problem?? -- Thanks in advance for your assistance! Coach Tony |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to retrive password protected worksheet but forgot password? | Excel Worksheet Functions | |||
password protected worksheet | Excel Discussion (Misc queries) | |||
Password protected worksheet | Excel Discussion (Misc queries) | |||
Exel Worksheet Protected With A Password | Excel Worksheet Functions | |||
how do i unhide password protected rows? | Excel Discussion (Misc queries) |