Pull up Excel's Sort Dialog

Question

I have to sort a protected sheet and need to perform the unprotect-sort-protect dance.

The problem is this way I'm duplicating (less than perfectly) Excel's own sorting functionality, with the predictable problems.

I've set the worksheet protected and selected to enable "Sort" on the "Allow users of this worksheet to" on the "Protect Sheet" window but this isn't working as expected.

I was thinking then to try pull up Excel's Sort dialog (see below). Is this exposed to us devs?

Answer 1

Try using an Auto Filter. That will allow you to keep the worksheet protected, and allow the user to sort columns.

Answer 2

This will show the Sort dialog (tested on XL 2010). If the current selection doesn't contain sortable content it will show a Msgbox with an error message. Be aware that if an entire column is selected, it won't prompt user to expand their selection as Excel normally does. One option is to select the UsedRange or another range before showing the dialog. You'll want to experiment.

Excel 2007 on has a new Sort object that stores fields to be sorted, but I don't see how it interacts with this dialog, if it does at all.

You can find a list of the xlDialogSort arguments here.

 Sub ShowSortDialog()

    On Error Resume Next
    Application.Dialogs(xlDialogSort).Show
    If Err.Number = 1004 Then
        MsgBox "Place the cursor in the area to be sorted"
    End If
    Err.Clear

    End Sub