Sort dialog box lock data headers vba

Question

All,

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;

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
 Application.Dialogs(xlDialogSort).Show
 If Err.Number = 1004 Then
    MsgBox "Place the cursor in the area to be sorted"
 End If
 Err.Clear


 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.

Answer 1

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
        r.AutoFilter
    End If

    Application.Dialogs(xlDialogSort).Show

    If Not HasFilter Then
        r.AutoFilter
    End If

End Sub