Sortdialog Box VBA

Question

All I have the below code which selects a variable range and displays the user with a sort dialog box. Which enables the user to sort the data.

The issue I face is that the sort dialog appears with the "My data has headers" box unticked. I need to force this to always be selected.

Any advice of how to ensure this is ticked would be much appreciated.

OtherExp.Range("B3:CE" & lastrow).Select

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

Answer 1

You can perform some workaround and force the .Header in the sort, using .Header = xlYes:

Sub TestMe()

    Dim mySort As Sort
    Dim myRng As Range: Set myRng = Range("A1:B3")
    Dim ws As Worksheet: Set ws = Worksheets(1)

    If Application.Dialogs(xlDialogSort).Show Then
        Set mySort = ws.Sort
        mySort.SetRange myRng
        mySort.Header = xlYes
        mySort.SortMethod = xlPinYin
        mySort.Apply
    End If

End Sub

Answer 2

Try

ActiveSheet.Sort.Header = xlYes
Application.Dialogs(xlDialogSort).Show