Use Excel's built in "My Table has Headers" functionality in VBA

Question

I use a userform to select an area containing data which is manipulated in various ways.

I do something like this to get the area:

If Selection.Cells.Count = 1 Then
    Set rng = ActiveCell.CurrentRegion
Else
    Set rng = Selection.Range
End If

MyDialog.SourceRange.Value = rng.Address
MyDialog.TableHasHeaders.Value = True

The userform has a tickbox called "My table has headers" set to True by default. I would like to control the default value of this tickbox using the same logic that Excel uses to control the default value of the tickbox with the same name that is shown when I click the Insert -> Table menu button.

I don't mind whether I show the built-in dialog and access the values of its fields or whether I call the function that controls the built-in tick box. Are either of these two things possible and if so, how?

Answer 1

Application.Dialogs(796).Show

gives the dialog, I don't know if it it's possible to retrieve some arguments with 'normal' vba code.

You could however, as a workaround, use the xlGuess option as an argument in the Listobject.add method, unlist it and retrieve the xlNo or xlYes which the application will give.

Public Function WhatIsTheGuess(myRange) As XlYesNoGuess
Dim bl As boolean

    With myRange.Parent
        .ListObjects.Add(xlSrcRange, myRange, , xlGuess).Name = "testing"
            bl = .ListObjects("testing").ListRows.Count = myRange.Rows.Count
        .ListObjects("testing").Unlist
            If bl Then myRange.Offset(-1).Rows(1).Delete
    End With

    WhatIsTheGuess = Abs(bl) + 1

End Function

Sub tst()
Dim mYTablehasHeaders As XlYesNoGuess, rng as range
Set Rng = Sheet1.Range("A1:A5")
 mYTablehasHeaders = WhatIsTheGuess(Rng)
End Sub