Hi,
I'm really struggling to workout how to programmatically add a listbox to an Excel sheet, populate it and then add a column header. Any help with this is much appreciated. The following returns a 438 error at the "LB.ColumnHeads = True" line.
Private Sub CommandButton1_Click()
Dim Filters As Worksheet
Dim ws1 As Worksheet
Dim FCount As Long
Dim FCol As Long
Dim First As Long
Dim FRange As Range
Dim LB As Object
Dim CB As Object
Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set Filters = ThisWorkbook.Worksheets("Filters")
FCount = WorksheetFunction.CountA(FA.Rows(1))
FCol = WorksheetFunction.Match(Me.ComboBox1.Value, Filters.Rows(1), 0)
Set FRange = Filters.Range(Filters.Cells(2, FCol), Filters.Cells(Filters.Rows.Count, FCol).End(xlUp))
Set LB = ws1.OLEObjects.Add(ClassType:="Forms.ListBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=14.4, Top:=165.8 , Width:=157.2, Height:=160.8)
LB.Name = Filters.Cells(1, FCol).Value & "LB"
LB.ListFillRange = "Filters!" & FRange.Address
LB.ColumnHeads = True
End Sub
Update: Made a bit of progress with this. I've realised that Sheet1.Listbox1.ColumnHeads = True works. Now can someone tell me if there's a way this can be applied by referencing the listbox by index like
Sheet1.OLEObjects(1).ColumnHeads = True or something?
Bookmarks