FOLLOW-UP to PREVENT USERS (PROTECTION)
 

I have a followup question on protecting the worksheets.
I had a similar problems couple weeks aback, worked my way around it
but still haven't found exactly what I'm looking for.

I need a way to protect the worksheet, while allowing users to sort
columns. I know one of the options under tools-- protection says i
allows sorting, but it does not work..
this is what I've done:

ActiveSheet.Unprotect ("*****")

Set sortrange = Application.InputBox(prompt:="Click a cell in th
column to be sorted", Type:=8)
sortrange.Select
Range("A1:AA6000").Sort Key1:=sortrange, Order1:=xlAscending
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
' Protect the worksheet
ActiveSheet.Protect ("******")

End Sub

As u can see, I had to write VB code to first unprotect the sheet
allow sorting then return the protection....

Is there a much simpler way to do this

--
Message posted from http://www.ExcelForum.com

FOLLOW-UP to PREVENT USERS (PROTECTION)
 

Here is a double click event macro that sorts the named range by the double
clicked column.
right click sheet tabview codeinsert this.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel
As Boolean)
mycol = ActiveCell.Column
[SortRange].Sort Key1:=Cells(1, ActiveCell.Column), Order1:=xlAscending,
Orientation:=xlTopToBottom
End Sub
=
Now for the protection. If you have xl2002 you may allow users to sort. If
not then I imagine that surrounding this with an uprotect/protect would work
..Untested
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel
As Boolean)
ActiveSheet.Unprotect ("*****")
[SortRange].Sort Key1:=Cells(1, ActiveCell.Column), Order1:=xlAscending,
Orientation:=xlTopToBottom
ActiveSheet.Protect ("******")
End Sub



--
Don Guillett
SalesAid Software

"krazylain " wrote in message
...
I have a followup question on protecting the worksheets.
I had a similar problems couple weeks aback, worked my way around it,
but still haven't found exactly what I'm looking for.

I need a way to protect the worksheet, while allowing users to sort,
columns. I know one of the options under tools-- protection says it
allows sorting, but it does not work..
this is what I've done:

ActiveSheet.Unprotect ("*****")

Set sortrange = Application.InputBox(prompt:="Click a cell in the
column to be sorted", Type:=8)
sortrange.Select
Range("A1:AA6000").Sort Key1:=sortrange, Order1:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
' Protect the worksheet
ActiveSheet.Protect ("******")

End Sub

As u can see, I had to write VB code to first unprotect the sheet,
allow sorting then return the protection....

Is there a much simpler way to do this?


---
Message posted from http://www.ExcelForum.com/