Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers

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 –  L. De Leo Mar 29 '11 at 15:25
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.