To exclude the headers (but including an empty 'row' at the end)
Private Sub UserForm_Initialize()
With GetObject(ThisDocument.Path & "\Book2.xlsx")
ListBox1.List = .sheets("Sheet Named Sue").Cells(1).currentregion.offset(1).Value
Close False
End With
ListBox1.ColumnCount = UBound(ListBox1.List, 2) + 1
End Sub
But if you use a table in the Excel file it can be done more elegantly
Private Sub UserForm_Initialize()
With GetObject(ThisDocument.Path & "\Book2.xlsx")
ListBox1.List = .sheets("Sheet Named Sue").Listobjects(1).DataBodyRange.value
Close False
End With
ListBox1.ColumnCount = UBound(ListBox1.List, 2) + 1
End Sub
- equivalents: Cells(1), Range("A1"), Cells(1,1)
- any range is possible as long as you load it's .values:
Private Sub UserForm_Initialize()
With GetObject(ThisDocument.Path & "\Book2.xlsx")
ListBox1.List = .sheets("Sheet Named Sue").range("C7:K20").Value
Close False
End With
ListBox1.ColumnCount = UBound(ListBox1.List, 2) + 1
End Sub