how to sort in a protected worksheet.

[Guest]

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.

 

[arno]

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

 

[Guest]

it's Excel 2003 SP2

 

[Robnific]

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

 

[Guest]

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.

 

[Dave Peterson]

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.

 

[Gord Dibben]

Then you will have to provide a macro to unprotect the sheet, do the sort then
re-protect the sheet.

First, under Tools>Protection>Protect 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

 

[Shaggyjh]

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]

You have an extra dot(.) in each of those two lines.

Remove one


Gord Dibben MS Excel MVP

 

[Ash]

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]

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

 

[Ash]

Thank you Gord!

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

 

[Lisa]

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

 

[microsoft]

Aha, I think I can get around this by setting my autofilters to start at row
2.

 

[Dave Peterson]

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]

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.

 

[Lisa]

Aha cracked it - with my sheet unprotected, I could right click on the cell
and see the rectangle

Thanks

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]

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)

Aha cracked it - with my sheet unprotected, I could right click on the cell
and see the rectangle

Thanks

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.