Can hide the first row in listbox ?

Mayozero

New Member
Joined
May 16, 2021
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Hello,

im trying to move the first row in list box into the header with this code is it possible ?
Untitled V7.0.1 - Copy.jpg


VBA Code:
Function show_data_in_listbox_test()
    Dim lastrow As Long, Row As Long, Col As Long, TempArray
    With Sheets("Database")
        lastrow = .Cells(Rows.Count, "A:A").End(xlUp).Row
        TempArray = .Range("A1:I" & lastrow).Value
        For Row = 1 To lastrow
            For Col = 3 To 6 '---select.name.for.the.columns; from 3 to 6 with Col= 3:6'
                TempArray(Row, Col) = Format(TempArray(Row, Col), "h:mm:ss AM/PM")
            Next Col
             TempArray(Row, 7) = Format(TempArray(Row, 7), "h:mm:ss")
             TempArray(Row, 8) = Format(TempArray(Row, 8), "h:mm:ss")
             TempArray(Row, 9) = Format(TempArray(Row, 9), "h:mm:ss")
        Next Row
    End With
    With ListBox1
        .ColumnCount = 9
        .ColumnWidths = "70,65,75,75,75,75,90,60,55,55"
        .List = TempArray
    End With
End Function

Thanks in advance
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,552
Office Version
  1. 365
Platform
  1. Windows
You can create another listbox, place it above the first listbox, populate the table header into it.

Listbox Header
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,826
Office Version
  1. 365
Platform
  1. Windows
Try setting the ColumnHeads property of the Listbox object to False...

VBA Code:
ListBox1.ColumnHeads = False

Actually, you can place it within your With/End With statement...

VBA Code:
    With ListBox1
        .ColumnCount = 9
        .ColumnHeads = False
        .ColumnWidths = "70,65,75,75,75,75,90,60,55,55"
        .List = TempArray
    End With

Hope this helps!
 

Mayozero

New Member
Joined
May 16, 2021
Messages
18
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Try setting the ColumnHeads property of the Listbox object to False...

VBA Code:
ListBox1.ColumnHeads = False

Actually, you can place it within your With/End With statement...

VBA Code:
    With ListBox1
        .ColumnCount = 9
        .ColumnHeads = False
        .ColumnWidths = "70,65,75,75,75,75,90,60,55,55"
        .List = TempArray
    End With

Hope this helps!
this line do the same when turn the "ColumnsHeads" to false so it's not also the expected one.
i want to keep the borders and the headers stays in it only, anyway thank u for trying :)
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,826
Office Version
  1. 365
Platform
  1. Windows
Have you considered using the RowSource property of the ListBox object to provide the list? For example, you could do the following...

VBA Code:
    Dim listRange As Range
    Dim lastRow As Long
    
    With Sheets("Database")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set listRange = .Range("A2:I" & lastRow)
    End With
    With ListBox1
        .ColumnCount = 9
        .ColumnHeads = True
        .ColumnWidths = "70,65,75,75,75,75,90,60,55,55"
        .RowSource = listRange.Address(External:=True)
    End With

If you must use the List property, you would have to find some sort of workaround. For example, you could create labels and place them above the listbox to serve as headers.
 
Solution

Mayozero

New Member
Joined
May 16, 2021
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Have you considered using the RowSource property of the ListBox object to provide the list? For example, you could do the following...

VBA Code:
    Dim listRange As Range
    Dim lastRow As Long
   
    With Sheets("Database")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set listRange = .Range("A2:I" & lastRow)
    End With
    With ListBox1
        .ColumnCount = 9
        .ColumnHeads = True
        .ColumnWidths = "70,65,75,75,75,75,90,60,55,55"
        .RowSource = listRange.Address(External:=True)
    End With

If you must use the List property, you would have to find some sort of workaround. For example, you could create labels and place them above the listbox to serve as headers.
thank you so much for the tip, when i lost hope to arrange the first row in the header row i used labels in the top and it helped alot
thank u so much again 😘
 

Forum statistics

Threads
1,147,958
Messages
5,744,043
Members
423,843
Latest member
alex2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top