Sort protected list

  • Thread starter Thread starter Ron King
  • Start date Start date
R

Ron King

Hi Everyone,
I have 4 sheets in a workbook, 3 calculate totals for 3 events, then I
bring them together on the fourth sheet to total the three events and find a
winner. This total sheet has all cells protected. Here is my problem, I
would like the totals for each person to be sorted in descending order so I
can quickly see the finishing order. Is there any way to either have the
totals sorted automatically as they go to the sheet, or possibly create a
macro and place it on the toolbar so that the totals can be sorted by anyone
without unprotecting the sheet? Any help would be grealty appreciated! I
searched excel help and the web all night last night and can't seem to find
anything. I'm not a programmer, but have a friend that could program
something in VB if that would be the best way to go.

Thanks in advance,
Ron
 
One feasible approach might be
to have it auto-sorted on another sheet ..

Assume the totals of the 3 events are in Sheet1,
cols A and B, data from row2 down:

Name TotalScore
ABC 99
DEF 101
KLM 101
XYZ 100
etc

Put in C2: =IF(B2<>"",B2-ROW()/10^10)
Copy down
(col C is an arbitrary tiebreaker col)

In Sheet2
--------------
A1:B1 will contain the same labels as in Sheet1

Put in A2:

=IF(ISERROR(MATCH(LARGE(Sheet1!$C:$C,ROW(A1)),Sheet1!$C:$C,0)),"",OFFSET(She
et1!$A$1,MATCH(LARGE(Sheet1!$C:$C,ROW(A1)),Sheet1!$C:$C,0)-1,COLUMN(A1)-1))

Copy across to B2, then fill down
as many rows as there is data in Sheet1

Cols A and B will return the list from Sheet1 in descending order,
viz. for the sample data above, it'll be:

Name TotalScore
DEF 101
KLM 101
XYZ 100
ABC 99
 
You may already know this, but when you set the protection, you can choose
to allow users to sort.

Click on Tools, then Protection, then Protect Sheet. Under the "Allow all
users of this worksheet to:" area, make sure Sort is selected.
 
This was added in xl2002.

But another option would be to use a macro that unprotects the worksheet, sorts
it, and then reprotects the worksheet.

If you put a button (from the forms toolbar) on the worksheet that has this
macro assigned to it, it'll make it easy for the users, too.
 
Hi Jamie,
I tried this first, but then everytime I try to sort it gives a message
that it can't sort because cells are protected. I've tried it a variety of
ways and still get the same message.

Ron
 
Hey Dave et al,
This is the option I decided to go with (a button with the macro you
suggested, see below), but a strange thing is happening. If I sort the list
and then close the workbook, the next time I come back to it, the protection
has changed from allowing users to select unlocked cells, to allowing users
to select locked or unlocked cells. It will not let the locked cells be
modified, but it is annoying me that it is changing the protection I've
saved. Any ideas? Is this just a hic-up in the program, or have I done
something wrong. I'm certain when I reprotect the sheet during the
recording of the macro that this is not the protection I'm selecting as I
've done this serveral times to be sure.

Ron
 
Based on the errors I make, I'd say it was the code <vbg>.

You may want to post what you have (and the version of excel).
 
Hi Dave here is the code:

Sub OpenSort()
'
' OpenSort Macro
' Macro recorded 8/20/2004 by Ron King
'
' Keyboard Shortcut: Ctrl+o
'
ActiveSheet.Unprotect
Range("B4:F53").Select
Selection.Sort Key1:=Range("F4"), Order1:=xlDescending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub

I can see were it unprotects and then protects, but I'm not sure what
DrawingObjects, Contents, and Scenarios are and why they are set to true.
Thanks in advance for your help.

Ron
 
Sorry, I forgot to mention I'm using Excel 2002 SP1

Ron

Ron King said:
Hi Dave here is the code:

Sub OpenSort()
'
' OpenSort Macro
' Macro recorded 8/20/2004 by Ron King
'
' Keyboard Shortcut: Ctrl+o
'
ActiveSheet.Unprotect
Range("B4:F53").Select
Selection.Sort Key1:=Range("F4"), Order1:=xlDescending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub

I can see were it unprotects and then protects, but I'm not sure what
DrawingObjects, Contents, and Scenarios are and why they are set to true.
Thanks in advance for your help.

Ron



Dave Peterson said:
Based on the errors I make, I'd say it was the code <vbg>.

You may want to post what you have (and the version of excel).
Click to expand...
the
list
Click to expand...
as
I
Click to expand...
events
and
Click to expand...
sorted
Click to expand...
 
Sorry, I didn't notice the real problem.

The enableselection portion of protection isn't remembered after you close the
workbook. You have to reset it each time you need it. A nice spot is in the
Auto_open (or workbook_open) procedure. (This had nothing to do with your sort
macro--it was just a coincidence that you noticed it then.)

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi"
.EnableSelection = xlUnlockedCells
End With
End Sub

The drawingobjects are shapes on the worksheet--if you want to stop the users
from messing with them you can protect them. (If you choose to protect them,
the user can't destroy them by mistake.)

In fact, next time you're in the VBE, click on .protect (in your code) and hit
F1. You'll see what these things mean--and lots of other info.





Ron said:
Hi Dave here is the code:

Sub OpenSort()
'
' OpenSort Macro
' Macro recorded 8/20/2004 by Ron King
'
' Keyboard Shortcut: Ctrl+o
'
ActiveSheet.Unprotect
Range("B4:F53").Select
Selection.Sort Key1:=Range("F4"), Order1:=xlDescending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub

I can see were it unprotects and then protects, but I'm not sure what
DrawingObjects, Contents, and Scenarios are and why they are set to true.
Thanks in advance for your help.

Ron
Click to expand...
 
Back
Top