Allow table sorting/filtering in protected sheet.

Hello,

I am new to the forum and having a problem.

I have a table with 12 or so columns of data for an end user. Data will continue to be entered down rows as the user aquires it. I want to protect the worksheet but retain the ability to sort and filter each column. I have this code so far:

Please Login or Register  to view this content.

but there are three problems:

1. I need this to be done on the fly, the user can't go run the macro each time he/she wants to sort, it needs to be automatic.

2. I need to be able to sort/filter all 12 columns of data, not just column A. and

3. If the user adds data in a row, this should also be included in the sort/filter (i.e. code needs to recognize where the last row containing data is located)

Any suggestions are much appreciated

Bob B

try this :


Private Sub Worksheet_Change(ByVal Target As Range)

With ThisWorkbook.Worksheets("Sheet1")
.Unprotect Password:="password"
.Range(.Range("A1").CurrentRegion.Address).sort _
Key1:=.Range("A1"), _
Order1:=1, _
Header:=1, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=1, _
DataOption1:=1
.Protect Password:="password", _
AllowFiltering:=True
End With

End Sub