Results 1 to 6 of 6

Thread: Excel VBA - MultiColumn ListBox Column Headers

  1. #1

    Thread Starter
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Excel VBA - MultiColumn ListBox Column Headers

    I have a multicolumn listbox (14 columns)

    It is on a userform.

    I have a horiztonal scrollbar as the listbox width exceeds the width of the userform.

    The problem is my column headers, which are nothing more than labels I have placed above each column (with much trial and error) on the userform itself.

    When I scroll horiztonally the reminder of my columns are displayed just fine, but the columns on the end have no headers because I have no screen space on which to place labels.

    Is there a way of creating column headers that are actually a part of the column rather than using the labels?

  2. #2
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Excel VBA - MultiColumn ListBox Column Headers

    Hack, Anhn has already showed you in the last thread on how to add column headers...

    Is that not what you want?

    Sid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  3. #3

    Thread Starter
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Excel VBA - MultiColumn ListBox Column Headers

    If you put .ColumnHeaders = True, column headers will be added.

    They are blank...how do I put specific text in each individual column header. I've played around with a variety of things and I can find no .Text property or .Value property or .Etc Property associated with the actual column header itself.

  4. #4
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Excel VBA - MultiColumn ListBox Column Headers

    You mean .ColumnHeads = True

    AFAIK you can't, you can only get headers if you bind the listbox to a range.

    To show the column headers create a temp sheet and hide it in your workbook and then import the data from the database into that temp sheet. Base your listbox from that temp sheet so that you can have column headers.

    Let me know if you want an example?

    Sid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  5. #5
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Excel VBA - MultiColumn ListBox Column Headers

    Here is an example (tested and tried see snapshot). You need to change only where mentioned below.

    The code assumes that

    a) You have a sheet named "Temp" in your workbook which is hidden.
    b) Your database name is Hack.Mdb which is in C:\
    c) You want a column width of 50

    Code:
    Private Sub CommandButton1_Click()
        Dim ws      As Worksheet
        Dim nm As Name
        '~~> Change below as applicable
        Const sDBPath = "C:\Hack.mdb"
        Const sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath & ";"
        Dim cn      As ADODB.Connection
        Dim rs      As ADODB.Recordset
        Dim sSQL    As String
        Dim i       As Long
        Dim LastRow As Long
        
        Set cn = New ADODB.Connection
        Set rs = New ADODB.Recordset
        '~~> Change below as applicable
        Set ws = Sheets("Temp")
        
        sSQL = "SELECT * FROM table1"
        cn.Open sConn
        rs.Open sSQL, cn, 1, 1
        
        For i = 1 To rs.Fields.Count - 1
            ws.Cells(1, i).Value = rs.Fields(i).Name
        Next
        
        LastRow = 2
        
        With Me.ListBox1
            .Clear
            .ColumnHeads = True
            If rs.RecordCount < 1 Then
                .ColumnCount = 1
                .AddItem "No record found"
            Else
                .ColumnCount = rs.Fields.Count
                Do While Not rs.EOF
                    For i = 1 To rs.Fields.Count - 1
                        If IsNull(rs.Fields(i)) Then ws.Cells(LastRow, i).Value = "" Else _
                        ws.Cells(LastRow, i).Value = rs.Fields(i)
                    Next
                    LastRow = LastRow + 1
                    rs.MoveNext
                Loop
                ActiveWorkbook.Names.Add Name:="Hack", _
                RefersToR1C1:="=Temp!R2C1:R" & LastRow & "C" & rs.Fields.Count
                .RowSource = "Hack"
                '~~> Change below as applicable
                .ColumnWidths = "50;50;50;50;50;50;50;50;50;50;50;50;50;50"
                .TopIndex = 0
            End If
        End With
        rs.Close: Set rs = Nothing
        cn.Close: Set cn = Nothing
    End Sub
    Hope this helps.

    Sid
    Attached Images Attached Images  
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  6. #6
    New Member
    Join Date
    Dec 2020
    Posts
    1

    Re: Excel VBA - MultiColumn ListBox Column Headers

    Was strugling for last 2 days... and I found this today... took half an hour to customize and job done... thanks SID...

Posting Permissions

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



Click Here to Expand Forum to Full Width