Sorting column in protected worksheet that has a macro

[kleclark]

Hi,
Firstly, I have a very limited Excel vocabulary and experience with macros so please forgive the awkwardness of my question.
I have created a protected excel worksheet sheet which contains macros that colleagues in different states fill in. My colleagues would like to be able to sort the data. </SPAN>
I tried to give this permission through the Review tab however this did not work. I have also tried setting the autofilter before protecting the sheet. Although this worked on a small practise sheet it did not work on my actual spreadsheet which is much larger and has a macro.</SPAN>
I have since looked for appropriate macros but my limited understanding of creating scripts means unless the macro is specific for my scenario I cannot use it. From my reading I gather I do not want to use the command userinterface as my colleagues in different states will not want to have reset the command each time they open the worksheet. I have since tried the following macro which did nothing:

Sub ProtectWithAutoFilterAndSortCapabilities()
With Sheets("Proposed")
ActiveSheet.Unprotect Password:="caveman", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True
End With
ActiveSheet.Protect Password:="caveman"
End Sub

I also realized I was unsure what this macro would allow me to do if it worked ie does this mean my colleagues can open the sheet go, to the sort tab and specify the column they want to sort and will all the columns be sorted in parallel.

So I was worried that I needed to specify a range to base the sorting on and tried this macro instead (unsuccessfully
0:
Sub Macro2()

Columns("A:AH").Select
ActiveWorkbook.Worksheets("Proposed").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Proposed").Sort.SortFields.Add Key:=Range("I3:I2000"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Proposed").Sort
.SetRange Range("A3:AH2000") .Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub

So as you can see I have made myself very confused. Can you please help me develop a macro for my specific purpose.

I have columns A-AH and the data starts in A3. I would like to specify a dynamic range for row numbers hence the A2000 (although currently there are only approximately 25 entries). I would like to sort the data based on column I which is a date. AND the sheet is protected.

Thank you for your consideration and please let me know if I need to provide additional informational or an example sheet

Kind regards
Kerri
</SPAN>
PS This is the macro on my large spreadsheet</SPAN>
Option Explicit
' Developed by Contextures Inc.
' www.contextures.com</SPAN>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
Me.Protect Password:="caveman", UserInterfaceOnly:=True
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 27 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
lUsed = InStr(1, oldVal, newVal)
If lUsed > 0 Then
If Right(oldVal, Len(newVal)) = newVal Then
Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
Else
Target.Value = Replace(oldVal, newVal & ", ", "")
End If
Else
Target.Value = oldVal _
& ", " & newVal
End If

End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub</SPAN>