2

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.

Built-in Dialog containing "My Table Has Headers" tickbox

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?

Community
  • 1
  • 1
High Plains Grifter
  • 1,357
  • 1
  • 12
  • 36

2 Answers2

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
EvR
  • 3,418
  • 2
  • 13
  • 23
  • This is a good workaround, and answers the question - there are possible problems with formatting and that, which I can workaround, but +1 for finding a way! – High Plains Grifter Jul 12 '18 at 15:52
0

This is what you get with the macro recorder:

Sub Macro1()
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$H$5"), , xlYes).Name = "Table1"
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$C$5"), , xlNo).Name = "Table2"
End Sub

As you see, whether or not you press on that square, is recorded as xlYes or xlNo in the parameters.

Accessing the function that compares the first two values of the row and then decides whether to propose a tick or not? Seems like you are asking Excel to show some source code. I guess it is not doable.

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • It is the default value I am interested in - sometimes it is ticked and sometimes unticked, apparently according to a comparison of the first row with the rest of the rows in the range - I wondered if it is possible to access the function that makes that comparison. – High Plains Grifter Jul 10 '18 at 09:21
  • @HighPlainsGrifter - accessing the function that compares the first two values of the row and then decides whether to propose a tick or not? Seems like you are asking Excel to show some source code. (I guess it is not doable) – Vityata Jul 10 '18 at 09:25
  • Yeah, that's what I thought to begin with, but figured I'd ask... I thought the dialog may be accessible as a whole unit or something... – High Plains Grifter Jul 10 '18 at 09:28
  • I cannot see anything likely here: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/built-in-dialog-box-argument-lists?f=255&MSPPError=-2147217396 – High Plains Grifter Jul 10 '18 at 09:43