Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sort in a protected worksheet.
i have a large worksheet with over 500 rows and 50 columns that needs to be
sent out to a large audience. how can I allow the worksheet to be sorted while it is protected? i have tried protecting only certain columns of data and tried to sort, but it only allows me to sort only the columns that are not protected and the data in each row entry isn't "expanded" the sort. -- /clm |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sort in a protected worksheet.
sent out to a large audience. how can I allow the worksheet to be sorted
while it is protected? pls. post your version of excel. there are new options in latest versions re. protected sheets. in excel 2003 for example, you will find an option to allow sorting in the dialog extras/protection/sheet protection. if you have older versions (i am not sure about 2002) then you must use macros for sorting. in the macro first unprotect the sheet, do the sort, protect the sheet (use screenupdating = false). arno |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sort in a protected worksheet.
it's Excel 2003 SP2
-- /clm "clm" wrote: i have a large worksheet with over 500 rows and 50 columns that needs to be sent out to a large audience. how can I allow the worksheet to be sorted while it is protected? i have tried protecting only certain columns of data and tried to sort, but it only allows me to sort only the columns that are not protected and the data in each row entry isn't "expanded" the sort. -- /clm |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sort in a protected worksheet.
Piece of Cake
open the page : unprotect all sort whatever you like protect show page or only protect page when you jump to another sheet. Only need basic VBE code :) www.allocator.nl Home of James 2007 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sort in a protected worksheet.
that won't work as i want to send the worksheet out to a large audience. the
data needs to be protected, but want to let the audience sort the data according to their needs. don't want them to change the data; just be able to sort. -- /clm "Robnific" wrote: Piece of Cake open the page : unprotect all sort whatever you like protect show page or only protect page when you jump to another sheet. Only need basic VBE code :) www.allocator.nl Home of James 2007 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sort in a protected worksheet.
Maybe you could give them a method that allows them to sort easily by clicking
on the header. You could assign a macro to hidden rectangles in the header rows. The macro would unprotect the sheet, sort by that column and reprotect the sheet. If you like that idea, you could start at Debra Dalgleish's site: http://www.contextures.com/xlSort02.html You'll have to add a couple of lines to unprotect and reprotect the worksheet, though. clm wrote: that won't work as i want to send the worksheet out to a large audience. the data needs to be protected, but want to let the audience sort the data according to their needs. don't want them to change the data; just be able to sort. -- /clm "Robnific" wrote: Piece of Cake open the page : unprotect all sort whatever you like protect show page or only protect page when you jump to another sheet. Only need basic VBE code :) www.allocator.nl Home of James 2007 -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sort in a protected worksheet.
Sorry to hijack this thread but i am trying to do the same thing, sort a
protected sheet. Excel 2003 I have protected the sheet allowing users to select unlocked and locked cells, as well as the Sort tick box is ticked. Password for the time being is (just quick and easy): a I have the following Macro assigned to a button called Sort: Sub Sort_Click() Dim coltosort As Range Set coltosort = Application.InputBox(Prompt:= _ "Select A Column", Type:=8) ActiveSheet.Unprotect Password:="a" ActiveSheet.UsedRange.Sort Key1:=coltosort.Cells(2), _ Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal With ActiveSheet ...Protect Password:="a" ...EnableSelection = xlNoRestrictions End With End Sub When i click on the button i get a Compile Error, Syntax Error with the options of Ok and Help. If i click Ok the debugger highlights the 1st line and the ..Protect password line: Sub Sort_Click() in yellow ...Protect Password:="a" in blue Also when the macro is in the debugger both these lines are in red font: ...Protect Password:="a" ...EnableSelection = xlNoRestrictions Sorry this is my 1st experience of the debugger! Any help would be greatly appreciated. "Gord Dibben" wrote: Then you will have to provide a macro to unprotect the sheet, do the sort then re-protect the sheet. First, under ToolsProtectionProtect Sheet allow users to "select locked cells" Then password protect with your choice of password. Assign this macro to button or shortcut key. Note: no error-checking. Sub sortit() Dim coltosort As Range Set coltosort = Application.InputBox(Prompt:= _ "Select A Column", Type:=8) ActiveSheet.Unprotect Password:="justme" ActiveSheet.UsedRange.Sort Key1:=coltosort.Cells(2), _ Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal With ActiveSheet ..Protect Password:="justme" ..EnableSelection = xlNoRestrictions End With End Sub Gord Dibben MS Excel MVP On Tue, 16 Oct 2007 09:30:01 -0700, clm wrote: that won't work as i want to send the worksheet out to a large audience. the data needs to be protected, but want to let the audience sort the data according to their needs. don't want them to change the data; just be able to sort. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sort in a protected worksheet.
You have an extra dot(.) in each of those two lines.
Remove one Gord Dibben MS Excel MVP On Mon, 3 Dec 2007 01:52:01 -0800, Shaggyjh wrote: Sorry to hijack this thread but i am trying to do the same thing, sort a protected sheet. Excel 2003 I have protected the sheet allowing users to select unlocked and locked cells, as well as the Sort tick box is ticked. Password for the time being is (just quick and easy): a I have the following Macro assigned to a button called Sort: Sub Sort_Click() Dim coltosort As Range Set coltosort = Application.InputBox(Prompt:= _ "Select A Column", Type:=8) ActiveSheet.Unprotect Password:="a" ActiveSheet.UsedRange.Sort Key1:=coltosort.Cells(2), _ Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal With ActiveSheet ..Protect Password:="a" ..EnableSelection = xlNoRestrictions End With End Sub When i click on the button i get a Compile Error, Syntax Error with the options of Ok and Help. If i click Ok the debugger highlights the 1st line and the ..Protect password line: Sub Sort_Click() in yellow ..Protect Password:="a" in blue Also when the macro is in the debugger both these lines are in red font: ..Protect Password:="a" ..EnableSelection = xlNoRestrictions Sorry this is my 1st experience of the debugger! Any help would be greatly appreciated. "Gord Dibben" wrote: Then you will have to provide a macro to unprotect the sheet, do the sort then re-protect the sheet. First, under ToolsProtectionProtect Sheet allow users to "select locked cells" Then password protect with your choice of password. Assign this macro to button or shortcut key. Note: no error-checking. Sub sortit() Dim coltosort As Range Set coltosort = Application.InputBox(Prompt:= _ "Select A Column", Type:=8) ActiveSheet.Unprotect Password:="justme" ActiveSheet.UsedRange.Sort Key1:=coltosort.Cells(2), _ Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal With ActiveSheet ..Protect Password:="justme" ..EnableSelection = xlNoRestrictions End With End Sub Gord Dibben MS Excel MVP On Tue, 16 Oct 2007 09:30:01 -0700, clm wrote: that won't work as i want to send the worksheet out to a large audience. the data needs to be protected, but want to let the audience sort the data according to their needs. don't want them to change the data; just be able to sort. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sort in a protected worksheet.
I'm truly hoping Gord is still out there! :) And I'm hoping my question is
not too dumb. What does it mean to assign the macro to button or shortcut key? I am doing the same thing as the original poster of this thread. I have a spreadsheet for which I am locking select cells to prevent data entry or manipulation. Now, I need to send the spreadsheet out to 50 users to allow data entry into 3 columns and numerous rows. I assume that once they receive the worksheet they will need/want to do some sorting. I have unlocked all cells that do not need to be locked. I have locked and password protected only those cells for which they should not change. I have Excel 2003 and in the Protection area have selected the box to allow users to select locked cells (but I confirmed they could not edit the data) and I've selected several other boxes to allow them to format, insert, but most especially, to sort. I've tried sorting and get the error that the sheet is protected. I assume this macro will work, but I don't know what it means to assign a button or shortcut key. Also, once I am able to do it, will I have to provide the users with instructions of how to run the macro? Thank you kindly for your time, Ashley "Gord Dibben" wrote: Then you will have to provide a macro to unprotect the sheet, do the sort then re-protect the sheet. First, under ToolsProtectionProtect Sheet allow users to "select locked cells" Then password protect with your choice of password. Assign this macro to button or shortcut key. Note: no error-checking. Sub sortit() Dim coltosort As Range Set coltosort = Application.InputBox(Prompt:= _ "Select A Column", Type:=8) ActiveSheet.Unprotect Password:="justme" ActiveSheet.UsedRange.Sort Key1:=coltosort.Cells(2), _ Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal With ActiveSheet ..Protect Password:="justme" ..EnableSelection = xlNoRestrictions End With End Sub Gord Dibben MS Excel MVP On Tue, 16 Oct 2007 09:30:01 -0700, clm wrote: that won't work as i want to send the worksheet out to a large audience. the data needs to be protected, but want to let the audience sort the data according to their needs. don't want them to change the data; just be able to sort. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sort in a protected worksheet.
Ashley
See David McRitchie's "getting started with macros and VBA" site. http://www.mvps.org/dmcritchie/excel/getstarted.htm Also Ron de Bruin's "where do I paste code" http://www.rondebruin.nl/code.htm Gord On Mon, 17 Dec 2007 07:07:01 -0800, Ash wrote: I'm truly hoping Gord is still out there! :) And I'm hoping my question is not too dumb. What does it mean to assign the macro to button or shortcut key? I am doing the same thing as the original poster of this thread. I have a spreadsheet for which I am locking select cells to prevent data entry or manipulation. Now, I need to send the spreadsheet out to 50 users to allow data entry into 3 columns and numerous rows. I assume that once they receive the worksheet they will need/want to do some sorting. I have unlocked all cells that do not need to be locked. I have locked and password protected only those cells for which they should not change. I have Excel 2003 and in the Protection area have selected the box to allow users to select locked cells (but I confirmed they could not edit the data) and I've selected several other boxes to allow them to format, insert, but most especially, to sort. I've tried sorting and get the error that the sheet is protected. I assume this macro will work, but I don't know what it means to assign a button or shortcut key. Also, once I am able to do it, will I have to provide the users with instructions of how to run the macro? Thank you kindly for your time, Ashley "Gord Dibben" wrote: Then you will have to provide a macro to unprotect the sheet, do the sort then re-protect the sheet. First, under ToolsProtectionProtect Sheet allow users to "select locked cells" Then password protect with your choice of password. Assign this macro to button or shortcut key. Note: no error-checking. Sub sortit() Dim coltosort As Range Set coltosort = Application.InputBox(Prompt:= _ "Select A Column", Type:=8) ActiveSheet.Unprotect Password:="justme" ActiveSheet.UsedRange.Sort Key1:=coltosort.Cells(2), _ Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal With ActiveSheet ..Protect Password:="justme" ..EnableSelection = xlNoRestrictions End With End Sub Gord Dibben MS Excel MVP On Tue, 16 Oct 2007 09:30:01 -0700, clm wrote: that won't work as i want to send the worksheet out to a large audience. the data needs to be protected, but want to let the audience sort the data according to their needs. don't want them to change the data; just be able to sort. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sort in a protected worksheet.
Thank you Gord!
"Gord Dibben" wrote: Ashley See David McRitchie's "getting started with macros and VBA" site. http://www.mvps.org/dmcritchie/excel/getstarted.htm Also Ron de Bruin's "where do I paste code" http://www.rondebruin.nl/code.htm Gord On Mon, 17 Dec 2007 07:07:01 -0800, Ash wrote: I'm truly hoping Gord is still out there! :) And I'm hoping my question is not too dumb. What does it mean to assign the macro to button or shortcut key? I am doing the same thing as the original poster of this thread. I have a spreadsheet for which I am locking select cells to prevent data entry or manipulation. Now, I need to send the spreadsheet out to 50 users to allow data entry into 3 columns and numerous rows. I assume that once they receive the worksheet they will need/want to do some sorting. I have unlocked all cells that do not need to be locked. I have locked and password protected only those cells for which they should not change. I have Excel 2003 and in the Protection area have selected the box to allow users to select locked cells (but I confirmed they could not edit the data) and I've selected several other boxes to allow them to format, insert, but most especially, to sort. I've tried sorting and get the error that the sheet is protected. I assume this macro will work, but I don't know what it means to assign a button or shortcut key. Also, once I am able to do it, will I have to provide the users with instructions of how to run the macro? Thank you kindly for your time, Ashley "Gord Dibben" wrote: Then you will have to provide a macro to unprotect the sheet, do the sort then re-protect the sheet. First, under ToolsProtectionProtect Sheet allow users to "select locked cells" Then password protect with your choice of password. Assign this macro to button or shortcut key. Note: no error-checking. Sub sortit() Dim coltosort As Range Set coltosort = Application.InputBox(Prompt:= _ "Select A Column", Type:=8) ActiveSheet.Unprotect Password:="justme" ActiveSheet.UsedRange.Sort Key1:=coltosort.Cells(2), _ Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal With ActiveSheet ..Protect Password:="justme" ..EnableSelection = xlNoRestrictions End With End Sub Gord Dibben MS Excel MVP On Tue, 16 Oct 2007 09:30:01 -0700, clm wrote: that won't work as i want to send the worksheet out to a large audience. the data needs to be protected, but want to let the audience sort the data according to their needs. don't want them to change the data; just be able to sort. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sort in a protected worksheet.
Hi
I have followed this excellent article for giving users the ability to sort a protected worksheet, http://www.contextures.com/xlSort02.html However, I have a problem in that I have the autofilters saved within the worksheet, and this piece of code conflicts with them - if I select a drop down arrow on the filter, it sorts the column as in the macro rather than giving me the drop down list. How can I get around this? If I can crack this, I will have made a big breakthrough in my quest Thanks in advance "clm" wrote: i have a large worksheet with over 500 rows and 50 columns that needs to be sent out to a large audience. how can I allow the worksheet to be sorted while it is protected? i have tried protecting only certain columns of data and tried to sort, but it only allows me to sort only the columns that are not protected and the data in each row entry isn't "expanded" the sort. -- /clm |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sort in a protected worksheet.
Aha, I think I can get around this by setting my autofilters to start at row
2. "Lisa" wrote in message ... Hi I have followed this excellent article for giving users the ability to sort a protected worksheet, http://www.contextures.com/xlSort02.html However, I have a problem in that I have the autofilters saved within the worksheet, and this piece of code conflicts with them - if I select a drop down arrow on the filter, it sorts the column as in the macro rather than giving me the drop down list. How can I get around this? If I can crack this, I will have made a big breakthrough in my quest Thanks in advance "clm" wrote: i have a large worksheet with over 500 rows and 50 columns that needs to be sent out to a large audience. how can I allow the worksheet to be sorted while it is protected? i have tried protecting only certain columns of data and tried to sort, but it only allows me to sort only the columns that are not protected and the data in each row entry isn't "expanded" the sort. -- /clm |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sort in a protected worksheet.
The rectangle floats over that cell (including the dropdown arrow). So you
really never got to the arrow. Maybe you could resize the rectangle so that it only uses the left half of the cell. Lisa wrote: Hi I have followed this excellent article for giving users the ability to sort a protected worksheet, http://www.contextures.com/xlSort02.html However, I have a problem in that I have the autofilters saved within the worksheet, and this piece of code conflicts with them - if I select a drop down arrow on the filter, it sorts the column as in the macro rather than giving me the drop down list. How can I get around this? If I can crack this, I will have made a big breakthrough in my quest Thanks in advance "clm" wrote: i have a large worksheet with over 500 rows and 50 columns that needs to be sent out to a large audience. how can I allow the worksheet to be sorted while it is protected? i have tried protecting only certain columns of data and tried to sort, but it only allows me to sort only the columns that are not protected and the data in each row entry isn't "expanded" the sort. -- /clm -- Dave Peterson |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sort in a protected worksheet.
Hi Dave
How can I make the rectangles visible in order to resize them (or perhaps the size is in the code somewhere?) Just wanted to say also, thanks so much for sharing that code, as a beginner (on a very fast track) it resulted in a huge breakthrough for what I am out to achieve. "Dave Peterson" wrote in message ... The rectangle floats over that cell (including the dropdown arrow). So you really never got to the arrow. Maybe you could resize the rectangle so that it only uses the left half of the cell. Lisa wrote: Hi I have followed this excellent article for giving users the ability to sort a protected worksheet, http://www.contextures.com/xlSort02.html However, I have a problem in that I have the autofilters saved within the worksheet, and this piece of code conflicts with them - if I select a drop down arrow on the filter, it sorts the column as in the macro rather than giving me the drop down list. How can I get around this? If I can crack this, I will have made a big breakthrough in my quest Thanks in advance "clm" wrote: i have a large worksheet with over 500 rows and 50 columns that needs to be sent out to a large audience. how can I allow the worksheet to be sorted while it is protected? i have tried protecting only certain columns of data and tried to sort, but it only allows me to sort only the columns that are not protected and the data in each row entry isn't "expanded" the sort. -- /clm -- Dave Peterson |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sort in a protected worksheet.
Aha cracked it - with my sheet unprotected, I could right click on the cell
and see the rectangle Thanks "Lisa" wrote in message ... Hi Dave How can I make the rectangles visible in order to resize them (or perhaps the size is in the code somewhere?) Just wanted to say also, thanks so much for sharing that code, as a beginner (on a very fast track) it resulted in a huge breakthrough for what I am out to achieve. "Dave Peterson" wrote in message ... The rectangle floats over that cell (including the dropdown arrow). So you really never got to the arrow. Maybe you could resize the rectangle so that it only uses the left half of the cell. Lisa wrote: Hi I have followed this excellent article for giving users the ability to sort a protected worksheet, http://www.contextures.com/xlSort02.html However, I have a problem in that I have the autofilters saved within the worksheet, and this piece of code conflicts with them - if I select a drop down arrow on the filter, it sorts the column as in the macro rather than giving me the drop down list. How can I get around this? If I can crack this, I will have made a big breakthrough in my quest Thanks in advance "clm" wrote: i have a large worksheet with over 500 rows and 50 columns that needs to be sent out to a large audience. how can I allow the worksheet to be sorted while it is protected? i have tried protecting only certain columns of data and tried to sort, but it only allows me to sort only the columns that are not protected and the data in each row entry isn't "expanded" the sort. -- /clm -- Dave Peterson |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to sort in a protected worksheet.
Glad you found a solution.
Another option would be to do the resizing in code. The line that adds the rectangle and sets its dimensions can be changed to this: Set myRect = .Parent.Shapes.AddShape _ (Type:=msoShapeRectangle, _ Top:=.Top, Height:=.Height, _ Width:=.Width / 2, Left:=.Left) Lisa wrote: Aha cracked it - with my sheet unprotected, I could right click on the cell and see the rectangle Thanks "Lisa" wrote in message ... Hi Dave How can I make the rectangles visible in order to resize them (or perhaps the size is in the code somewhere?) Just wanted to say also, thanks so much for sharing that code, as a beginner (on a very fast track) it resulted in a huge breakthrough for what I am out to achieve. "Dave Peterson" wrote in message ... The rectangle floats over that cell (including the dropdown arrow). So you really never got to the arrow. Maybe you could resize the rectangle so that it only uses the left half of the cell. Lisa wrote: Hi I have followed this excellent article for giving users the ability to sort a protected worksheet, http://www.contextures.com/xlSort02.html However, I have a problem in that I have the autofilters saved within the worksheet, and this piece of code conflicts with them - if I select a drop down arrow on the filter, it sorts the column as in the macro rather than giving me the drop down list. How can I get around this? If I can crack this, I will have made a big breakthrough in my quest Thanks in advance "clm" wrote: i have a large worksheet with over 500 rows and 50 columns that needs to be sent out to a large audience. how can I allow the worksheet to be sorted while it is protected? i have tried protecting only certain columns of data and tried to sort, but it only allows me to sort only the columns that are not protected and the data in each row entry isn't "expanded" the sort. -- /clm -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to sort protected worksheet in Excel 07? Doesn't work. | Excel Worksheet Functions | |||
How do I sort a protected worksheet? | Excel Discussion (Misc queries) | |||
Sort Columns in Protected Worksheet, etc. | Excel Worksheet Functions | |||
How to Sort Protected Worksheet | Excel Worksheet Functions | |||
Is it possible to sort a protected worksheet? | Excel Discussion (Misc queries) |