List objects are really cool, they provide the ability to load these objects on Excel from data types outside your spreadsheet, like Text Files, Comma Delimited, XML, HTML, JSON, Direct Database Connections, websites, and other Excel files. The ListObjects when created have a name and can be referenced on the spread sheet by just using a vlookup formula on that object. They also look nice and have built in template color schemes, plus the added benefit of an auto query feature, nothing gets out of order and it's very professional. The question in my simple mind is... how can I get to this object to muck about with it in my program. How can I load these tables, and then use them as a source for form objects, like pull downs and radio buttons?
here are some great sources of information:
Microsoft Excel Documentation - General Microsoft documentation, no good examples
Spreadsheet Guru - Fantastic examples of code
CodeVBA
https://stackoverflow.com/questions/49625994/excel-listobject-table-listrows-count-vs-range-rows-count
https://stackoverflow.com/questions/50076973/vba-populate-listbox-from-multiple-listobjects
Private Sub Load_bttn_Click()
'load productmfg table
Dim lo As ListObject
Dim i As ListRow
Set lo = ActiveWorkbook.Worksheets("cat_ref").ListObjects("Vendor_List")
Me.product_mfg_list.Clear
For Each i In lo.ListRows
Me.product_mfg_list.AddItem i.Range.Formula
Next
End Sub
Private Sub product_mfg_list_Change()
Dim s As String
Dim r As ListRow
s = product_mfg_list.value
GetRange("Output_ProductManufacturer").value = s ' Me.Range("L28").Value
Me.sub_product_list.Clear
For Each r In ActiveWorkbook.Sheets("cat_ref").ListObjects("Vendor_Ref_Table").ListRows
If r.Range(1, 1).value = s Then
Me.sub_product_list.AddItem r.Range(1, 2)
End If
Next
End Sub
