Results 1 to 5 of 5

Programmatically Add Column Headers to Worksheet Based OLE ListBox

Threaded View

  1. #1
    Registered User
    Join Date
    11-13-2009
    Location
    Cumbria
    MS-Off Ver
    Excel 2016
    Posts
    87

    Programmatically Add Column Headers to Worksheet Based OLE ListBox

    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?
    Last edited by RowanB; 08-11-2021 at 11:15 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Listbox Userform Column Headers -How To ?
    By Logit in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-09-2018, 08:09 AM
  2. display column headers on listbox and filtering using several textbox
    By m.zaeim in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-17-2016, 05:31 PM
  3. Userform Listbox Column headers from recordset or array?
    By puuts in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-10-2016, 09:33 AM
  4. [SOLVED] Adding column headers to listbox
    By devatu in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-26-2015, 07:44 AM
  5. [SOLVED] listbox with column headers as a specific sheet column headers
    By ANDREAAS in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-07-2014, 04:40 AM
  6. [SOLVED] How to show column headers in listbox from access database?
    By Kiran2012 in forum Access Programming / VBA / Macros
    Replies: 4
    Last Post: 11-26-2012, 10:30 AM
  7. [SOLVED] Listbox column headers when data is collected in an array
    By evertjvr in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-12-2012, 09:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1