VBA does not recognize headers from Listed Objects

Question

I cannot understand why excel stopped recognizing the headers of listed objects; specifically this line Range("Offer_Table[[#Headers], [LOC]").Select I would like to change the format of specific header and I first was able to do it using the following code.

Range("Offer_Table[[#Headers], [LOC]").Select
    With Selection.Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = -0.249977111
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With

however, the code stopped working once I closed all active sheets to run a first complete test on a new sheet. I do not understand why but headers now are no longer recognized by Excel. Either if I create a new LO from VBA or I do it manually setting a new table.

Below is the sample code

'Referring to the Table
    Dim Offer_table As ListObject
    CntCol = Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Count
    CntRow = Range(Cells(1, 2), Cells(1, 2).End(xlDown)).Count


MsgBox CntCol
MsgBox CntRow
    Set Offer_table = ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(1, 1), Cells(CntRow, CntCol)), , xlYes)
    Offer_table.Name = "Offer_Table"
    Offer_table.TableStyle = "TableStyleLight2"


'Highlight headers
        Range("Offer_Table[[#Headers], [Order Creation]").Select
    With Selection.Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = -0.249977111
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With

         Range("Offer_Table[[#Headers],[SEGMENT]").Select
    With Selection.Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = -0.249977111
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and [The VBA Guide To ListObject Excel Tables](https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables). — Pᴇʜ, May 02 '19 at 12:49
Also note that `Range("Offer_Table[[#Headers], [LOC]").Select` should be `Range("Offer_Table[[#Headers], [LOC]]").Select`. Since there are two opening brackets, there should also be two closing brackets as well. — Mistella, May 02 '19 at 12:55

score 0 · Answer 1 · answered May 02 '19 at 13:28

I think your error is caused due to you not explicitly referencing where your table is. You also declare it as a ListObject and then don't use it when referring to it later on. Have a look at the following. Make sure to update the With Activesheet to the explicit reference of the sheet with the table on otherwise as soon as you move to another sheet you will run into issues again

'Referring to the Table
Dim Offer_table As ListObject
Dim CntCol As Long, CntRow As Long

' Update with reference to your sheet
With ActiveSheet
    CntCol = Range(.Cells(1, 1), .Cells(1, 1).End(xlToRight)).Count
    CntRow = Range(.Cells(1, 2), .Cells(1, 2).End(xlDown)).Count

    MsgBox CntCol & vbNewLine & CntRow

    Set Offer_table = ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(1, 1), Cells(CntRow, CntCol)), , xlYes)
    Offer_table.Name = "Offer_Table"
    Offer_table.TableStyle = "TableStyleLight2"
End With

'Highlight headers
' Range("Offer_Table[[#Headers], [Order Creation]").Select
With Offer_table
    With .HeaderRowRange(.ListColumns("Order Creation").Range.Column)
        With .Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = 11
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = -0.249977111
            .ThemeFont = xlThemeFontMinor
        End With
        With .Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent4
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
    End With

    ' Range("Offer_Table[[#Headers],[SEGMENT]").Select
    With Offer_table.HeaderRowRange(.ListColumns("SEGMENT").Range.Column)
        With .Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = 11
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = -0.249977111
            .ThemeFont = xlThemeFontMinor
        End With
        With .Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent4
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
    End With
End With

VBA does not recognize headers from Listed Objects

1 Answers1