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.
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 |
#8
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 |
#9
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 |
#10
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 |
#11
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 |
#12
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) |