Sorting column in protected worksheet using a macro

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 colleagues in different states fill in. My colleagues would like to be able to sort the data. I tried to give this permission through the Review tab however after reading many forum sites it appears this feature is almost cosmetic and not what I am after.
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 are even less savvy them me and 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.

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

Good morning TMShucks. My apologies for not respondning sooner.
I have attached an example spreadsheet as you requested. I had previously tried to allow my colleagues to sort the protected sheet by clicking the review tab and selecting to protect the sheet. I have ticked the allow to sort box and entered a password however this did not work.
I greatly appreciate your time and thoughts.

Also, when I entered the thread I entered tags into the tags box. Have these tags not appeared or are there additional tags I need to enter. Can you tell me how I can enter them now?

Best
Kerri

Originally Posted by TMShucks

See the attached updated example. Note that it is password protected but the password is blank. I have switched on Autofilter.


You need to unlock the cells that you want to sort ... that is the data area. I have set the cell format to unlocked on the first four columns.

I don't think it is a good idea to have Data Validation all the way down to the bottom of the worksheet. It seemed very slow when I opened the workbook and I suspect that it's down to that. I've removed it from row 21 down.

The best way to propagate formats and formulae is to convert the data to a Table.

I hope this helps.


Regards, TMS

Hi TMS

Thank you for your suggestions. While it was able to work on the example sheet I sent you it does not appear to work on a larger sheet with macros on it. I have attached another example sheet which has the macros and formulas I am using. Could you please have a look at this one for me. I removed the data validation on all rows after row 20. I also tried unmerging the top rows with no luck. When I apply the changes you recommended excel becomes unresponsive when the sheet is protected. If the sheet is not protected the sort occurs in a matter of seconds. Also, with the autofilter, if I have locked cells in columns, will these cells also be sorted according to the unlocked cells ie I need to sort all cells in the sheet dependent on the column selected for autofiltering.

Scrap the previous comment I have tried to upload the file however it is too big - 11.3mb. Do you have any suggestions on how I can share this spreadsheet with you please? There is no data on this sheet, I have removed the data validation from rows 21 onwards however it does have a macros below.

Thank you for your help. It is invaluable.

Sincerely
Kerri

Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
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