Excel VBA referancing wrong table on sheet-CodePudding

I have three tables on a single sheet tblMaintenanceData, tblVehicleDailyLog, and tblDriverList. And use forms to fill the tables. These forms use ComboBox Dropdown lists, filled from the data from these tables -- one for Vehicle ID, a second for Driver Name.

The vehicle ID Dropdown List fills properly. The Driver Name Dropdown does not; rather throwing an error when it starts to load data into the Dropdown List (italicized and bold line below).

Using Debug.Print I have discovered while iRows and iColumns fills with the correct values. Cells(x, y).Value points to the first table on the page (tblMaintenanceData), rather than tblDriverList. So what am I doing wrong?

Thanks

Sub FillDriverList()


 '  This fills the drop down list of available drivers for each of the user created forms.
    Const WorkSheetName = "MaintenanceData"
    Const WorkTableName = "tblDriverList"

    Dim tbl As ListObject
    Dim lRows As Long, lColumns As Long, lWork01 As Long
    Dim i As Integer
    Dim ws As Worksheet
    Dim CurrentPageState As String
    Dim CurrentPageProtection As String

    Set ws = ThisWorkbook.Sheets(WorkSheetName)

    ' this saves the Page State for Visibility and Protection, allowing to be reset after working with the page
    CurrentPageState = ws.Visible
    CurrentPageProtection = ws.ProtectContents
    ws.Visible = xlSheetVisible
    ws.Select

    Set tbl = ws.ListObjects(WorkTableName)

    With tbl.DataBodyRange
        lRows = .Rows.Count
        lColumns = .Columns.Count
    End With

'Debug.Print lRows & " / " & lColumns

    For i = 1 To lColumns
        If Cells(1, i).Value = "DRIVER LIST" Then lWork01 = i

'Debug.Print Cells(1, i).Value

    Next

'Debug.Print Cells(2, 1).Value & " - " & Cells(3, 1).Value & " - " & Cells(4, 1).Value

    For i = 1 To lRows
        With DataEntry06
            ***.cmbInput05.AddItem ws.Cells(i, lWork01).Value***
        End With
    Next


CLOSE_SUB:
    ws.Visible = CurrentPageState
    If CurrentPageProtection = True Then
        ws.Protect UserInterfaceOnly:=True
    End If

    Set ws = Nothing
    Set tbl = Nothing


End Sub

View of Data Tables View of Data Tables

CodePudding user response：

Try "tbl.DataBodyRange.Select" instead of With / End With

CodePudding user response：

I think you're doing more work than you need to here - you can leverage the listobject properties and methods more.

Simplified:

Sub FillDriverList()
    
    Const WorkSheetName = "MaintenanceData"
    Const WorkTableName = "tblDriverList"

    Dim tbl As ListObject
    
    Set tbl = ThisWorkbook.Sheets(WorkSheetName).ListObjects(WorkTableName)
    
    'loop each cell in the "DRIVER LIST" column
    For Each c In tbl.ListColumns("DRIVER LIST").DataBodyRange.Cells
        DataEntry06.cmbInput05.AddItem c.Value
    Next c

End Sub