invalid use of property when dealing with list objects

Question

Im getting an invalid use of property error on the line where I try to assign the formatted table name to tables. I'm assuming this is a syntactical issue. However, I'm not sure what I want to do is possible without more code or different code. I want to grab the tablename of the activesheet so that in the next line it can be added in the range to create formatted table syntax(i.e. MyTable[EntityID])

Option Explicit

Private Sub Workbook_Open()

Dim ws As Worksheet
Dim wbkCurBook As Workbook
Dim searchValue As String
Dim searchSheet As String
Dim tableMatch As Range
Dim cell As Range
Dim combined As String
Dim x As Long
Dim LastColumn As Long
Dim tables As ListObject

Application.ScreenUpdating = False

Set wbkCurBook = ActiveWorkbook

'highlight all entityIDs with missing definitions
    For x = 3 To Sheets.Count
        Sheets(x).Activate
        ws = ActiveSheet
        tables = Sheets(x).ListObjects
                For Each cell In Sheets(x).Range(tables & "[EntityID]")
                    searchValue = cell.Value
                    searchSheet = Sheets(x).Name
                    combined = searchSheet & " " & searchValue
                    
                    With wbkCurBook.Sheets("Data Dictionary").Range("Dictionary[CombinedName]")
                            Set tableMatch = .Find(What:=combined, _
                                            After:=.Cells(.Cells.Count), _
                                            LookIn:=xlValues, _
                                            LookAt:=xlWhole, _
                                            SearchOrder:=xlByRows, _
                                            SearchDirection:=xlNext, _
                                            MatchCase:=False)

                        If tableMatch Is Nothing Then
                                cell.Interior.Color = RGB(255, 0, 0)
                        End If
                    End With
                Next cell
    Next x
    
    Application.ScreenUpdating = True
        
 End Sub

Answer 1

Highlight Missing

• The code should be copied into a standard module e.g. Module1, but you can copy it to the ThisWorkbook module. Then, in your Open event procedure (in the ThisWorkbook module), just use the line HighlightMissingDefinitions.

Option Explicit

Sub HighlightMissingDefinitions()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code

    ' Source
    Dim sws As Worksheet: Set sws = wb.Worksheets("Data Dictionary")
    Dim stbl As ListObject: Set stbl = sws.ListObjects("Dictionary")
    Dim srg As Range
    Set srg = stbl.ListColumns("CombinedStringName").DataBodyRange
    Dim sCell As Range
    
    ' Destination
    Dim dws As Worksheet
    Dim dtbl As ListObject
    Dim drg As Range
    Dim dCell As Range
    Dim d As Long
    Dim dWorksheetName As String
    Dim dSearchString As String
    Dim dCombinedString As String
    
    Application.ScreenUpdating = False
    
    ' Highlight
    For d = 3 To wb.Worksheets.Count
        Set drg = Nothing
        On Error Resume Next
        Set dws = wb.Worksheets(d)
        Set dtbl = dws.ListObjects(1)
        Set drg = dtbl.ListColumns("EntityID").DataBodyRange
        On Error GoTo 0
        If Not drg Is Nothing Then
            drg.Interior.Color = xlNone
            For Each dCell In drg.Cells
                dSearchString = CStr(dCell.Value)
                dWorksheetName = dws.Name
                dCombinedString = dWorksheetName & " " & dSearchString
                ' You could just do:
                'dCombinedString = dws.Name & " " & CStr(dCell.Value)
                ' No need for the extra two variables.
                Set sCell = srg.Find( _
                    What:=dCombinedString, _
                    After:=srg.Cells(srg.Cells.Count), _
                    LookIn:=xlFormulas, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows) ' 'xlNext' and 'False' are default
                If sCell Is Nothing Then
                    dCell.Interior.Color = vbRed ' vbYellow, vbGreen
                End If
            Next dCell
        End If
    Next d
    
    Application.ScreenUpdating = True
        
End Sub

Optionally

Sub MatchInsteadOfFind()
    ' Instead of 'Dim sCell As Range' use:
    Dim srIndex As Variant
    ' Instead of 'Set sCell...' use:
    srIndex = Application.Match(dCombinedString, srg, 0)
    ' Instead of 'If sCell...' use:
    If IsError(srIndex) Then
        dCell.Interior.Color = vbRed ' vbYellow, vbGreen
    End If
End Sub

Answer 2

Since you have declared Dim tables As ListObject, you have to use SET. Also you will have to tell which table you want to work with.

Change

tables = Sheets(x).ListObjects

to

Set tables = Sheets(x).ListObjects(1)

And then use it like tables.Name

For example

Dim tables As ListObject

Set tables = Sheets(1).ListObjects(1)

Debug.Print tables.Name & "[EntityID]"