I am using some code to bring up the sort dialog box via VBA. My data set will always have a header and I want to lock "My data has headers button in the corner of the sort dialog box"

I have inserted the line

`ActiveSheet.Sort.Header = xlYes`

However this does not seem to be acting in the way I would expect it to. The result I wish to obtain is within the screen shot below;

enter image description here

Full code below;

Sub ShowSortDialogBRR()

 Application.ScreenUpdating = False
 Application.Calculation = xlManual
 ActiveSheet.Unprotect Password:="fsp123"
 Application.EnableEvents = False

 'select range and show sort dialog box

 Dim Lastrow As Long
 Lastrow = ActiveSheet.Range("LastRow_BRR").Offset(rowOffset:=-1).Row
 Brr.Range("B3:CE" & Lastrow).Select

 On Error Resume Next
 ActiveSheet.Sort.Header = xlYes
 If Err.Number = 1004 Then
    MsgBox "Place the cursor in the area to be sorted"
 End If

 With ActiveSheet
.Protect Password:="fsp123", UserInterfaceOnly:=True, DrawingObjects:=False, Contents:=True, AllowFiltering:=True, AllowFormattingColumns:=True
.EnableOutlining = True
End With

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Application.EnableEvents = True

End Sub

any help to resolve this matter would be much appreciated.


That option is grayed out when the range has a filter applied. You don't have to actually filter the data, just have filter dropdowns showing. Here's an example that turns on the fitlers if they're not already.

Sub SortData()

    Dim r As Range
    Dim HasFilter As Boolean

    Set r = Sheet1.Range("A1:B4")

    HasFilter = Sheet1.AutoFilterMode

    If Not HasFilter Then
    End If


    If Not HasFilter Then
    End If

End Sub
  • Thanks for the explanation it works perfectly :) – SB999 Jul 18 '18 at 7:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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