Nevertheless, a search around the net suggests the best way to do this is to use a macro to un-protect the sheet, run the sort & then re-protect the sheet. The first way that I've done this is to add two buttons to my worksheet to sort a particular range ("data") dependent on what column the user currently has selected.
Here's the code:
Private Sub cmdSortAsc_Click()
cmdSort (xlAscending)
End SubPrivate Sub cmdSortDesc_Click()
cmdSort (xlDescending)
End SubPrivate Sub cmdSort(order As Long)
'unprotect the sheet
ActiveSheet.Unprotect Password:="sunshine"
'calculate required parameters
Dim current_cell As String 'the currently selected cell
Dim current_column As String 'the column of the currently selected cell
Dim data_range_address As String 'the address of the "data" range
Dim top_row As String 'the top row of the "data" range
current_cell = ActiveCell.Address
current_column = Left(current_cell, InStr(InStr(current_cell, "$") _
+ 1, current_cell, "$") - 1)
data_range_address = Range("data").Address
top_row = Mid(data_range_address, InStr(InStr(data_range_address, "$") + 1, _
data_range_address, "$") + 1, _
InStr(data_range_address, ":") - InStr(InStr(data_range_address, "$") + 1, _
data_range_address, "$") - 1)
InStr(data_range_address, ":") - InStr(InStr(data_range_address, "$") + 1, _
data_range_address, "$") - 1)
'sort using the calculated parameters
Range("data").Sort Key1:=Range(current_column + top_row), Order1:=order
'snap the selection to the active cell to show user what was used to select
ActiveCell.Select
're-protect the sheet
ActiveSheet.Protect Password:="sunshine"
End Sub
No comments:
Post a Comment