adding comments to ListColumn tables in Excel with Access VBA

[TysonC]

My first post. I can usually find what I need and don't need to post but I've come up empty on this one.
I am exporting several tables from Access to Excel 2013 with VBA and then formatting the sheets as tables, adding NumberFormats and so on.
My titles are abbreviated so I want to add verbose descriptions in a comment.
I've tried all the combinations of .Comment and AddComment I can think of.

xls.Application.Workbooks.Open (xlsxPath)
Set wkb = xls.Application.ActiveWorkbook
Set wks = wkb.Worksheets("tblKPI_Hourly")
myName = "HourlyKPI"
wks.ListObjects.Add(xlSrcRange, wks.Range("A1").CurrentRegion, , xlYes).Name = myName
Set tbl = wks.ListObjects(myName)
tbl.HeaderRowRange("tIn").Address.AddComment = "Total Inbound Contacts"

This returns Run-time error 5: Invalid procedure call or argument at the last line.

Thanks in advance.

 

[Norie]

Which column(s) do you want to add the comment(s) to?

HeaderRowRange will return the entire header row of the table, so you'll need to specify the specific header/column you want to add the comment to.

PS You need to drop Address too.

 

[TysonC]

The column that has the header "tIn" for this example.
I can also use tbl.ListColumns(c) where c is the column number.
I put address in because .comment seems to need an address.
I did find that tbl.HeaderRowRange("tIn") is the part that was causing that particular error, but I don't know what to do about it.
tbl.HeaderRowRange(c).Value works to get "tIn" from the column number.

 

[Norie]

I'm afraid you can't specify the column using HeaderRowRange(fieldname).

HeaderRowRange actually returns a range object, so using HeaderRowRange(c).Value will return the value from the cth cell in the range.

You could use something like this.

Dim tbl As ListObject
Dim col As ListColumn
Dim hdr As Range

    Set tbl = Sheet1.ListObjects(1)
    
    Set hdr = tbl.HeaderRowRange
    
    Set col = tbl.ListColumns("tIn")
    
    Intersect(col.Range, hdr).AddComment "Total Inbound Contacts"

 

[TysonC]

I threw

Intersect(tbl.ListColumns("tIn").Range, tbl.HeaderRowRange).AddComment "Total Inbound Contacts"

in and did not get an error but did not get a comment either.

Thanks for the help!

 

[TysonC]

I lie. It worked! Thank you!

 

[TysonC]

Cross posted to
vbaexpress.com/forum/showthread.php?55368-adding-comments-to-ListColumn-tables-in-Excel-with-Access-VBA
before I knew cross posting was bad.
so far, this is the best solution.

 

[Norie]

This actually works, provided there's no comment in the cell already and you don't use =.

tbl.ListColumns("tIn").Range.Cells(1).AddComment "Total Inbound Contacts"

I'd actually tried that with = but there was an error, however a comment had been added so when I removed the = I got an error because there was an existing comment.

It might be worth trying something like this to delete any existing comment.

    Set tbl = Sheet1.ListObjects(1)

    With tbl.ListColumns("tIn").Range.Cells(1)
        If Not .Comment Is Nothing Then
            .Comment.Delete
        End If
        .AddComment "Total Inbound Contacts"
    End With

 

[TysonC]

My final code:

   For c = 1 To lastCol        HRR = tbl.HeaderRowRange(c).Value
        
        'Add Comments
        Desc = DLookup("Description", "settings_kpi", "[Field]='" & HRR & "'")
        With tbl.ListColumns(HRR).Range.Cells(1)
            If Not .Comment Is Nothing Then
                .Comment.Delete
            End If
            .AddComment Desc
        End With
more

Thank you so very much!
-Tyson