Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

enter image description here

share|improve this question
You might just try the Range.Sort method in VBA. – Lance Roberts Jun 11 '11 at 0:31

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

share|improve this answer
That is selected too, but it's not working on the protected sheet – Lorenzo Mar 29 '11 at 15:25

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
    If Err.Number = 1004 Then
        MsgBox "Place the cursor in the area to be sorted"
    End If

    End Sub
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .