Cant Get Listbox Headings On Top Row!!


Well-known Member
Apr 6, 2005
Hi Folks,

I am using this code to place ainformation in a listbox.

In the properties window i have set the ColumnHeads to "TRUE" and so when the listbox opens the top line shows the lined boxes where the headers should go.

However when i use the code below to place the details in the listbox the headers appear in the second line below where they should be.

Can anyone tell me where i am going wrong please?


Private Sub FindAllButton_Click()
    Dim FirstAddress As String
    Dim strFind As String    'what to find
    Dim rSearch As Range  'range to search
    Dim fndA, fndB, fndC, fndD, fndE, fndF, fndG, fndH, fndJ, fndK, fndL, fndM As String
    Dim head1, head2, head3, head4, head5, head6, head7, head8, head9, head10, head11 As String
    Dim I As Integer, intC As Integer
    I = 1
    Application.ScreenUpdating = False
    'need to select archive page,screen updating off to hide this
    Set rSearch = Worksheets("Archive").Range("B7", Range("B65536").End(xlUp))
    strFind = Me.TextBox5.Value
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            head1 = Range("B6").Value
            head2 = Range("C6").Value
            head3 = Range("D6").Value
            head4 = Range("E6").Value
            head5 = Range("F6").Value
            head6 = Range("G6").Value
            head7 = Range("H6").Value
            head8 = Range("J6").Value
            head9 = Range("K6").Value
            head10 = Range("L6").Value
            head11 = Range("M6").Value
            With Me.ListBox1
                MyArray(0, 0) = head1 'Roy - changed this,ListBox must start from column 0
                MyArray(0, 1) = head2
                MyArray(0, 2) = head3
                MyArray(0, 3) = head4
                MyArray(0, 4) = head5
                MyArray(0, 5) = head6
                MyArray(0, 6) = head7
                MyArray(0, 7) = head8
                MyArray(0, 8) = head9
                MyArray(0, 9) = head10
                MyArray(0, 10) = head11
                MyArray(0, 11) = head12
            End With
            FirstAddress = c.Address
                'Load details into Listbox
                fndA = c.Value
                fndB = c.Offset(0, 1).Value
                fndC = c.Offset(0, 2).Value
                fndD = c.Offset(0, 3).Value
                fndE = c.Offset(0, 4).Value
                fndF = c.Offset(0, 5).Value
                fndG = c.Offset(0, 6).Value
                fndH = c.Offset(0, 8).Value
                fndJ = Format(c.Offset(0, 9).Value, "h:mm")
                fndK = c.Offset(0, 10).Value
                fndL = c.Offset(0, 11).Value
                fndM = c.Offset(0, 12).Value

                MyArray(I, 0) = fndA
                MyArray(I, 1) = fndB
                MyArray(I, 2) = fndC
                MyArray(I, 3) = fndD
                MyArray(I, 4) = fndE
                MyArray(I, 5) = fndF
                MyArray(I, 6) = fndG
                MyArray(I, 7) = fndH
                MyArray(I, 8) = fndJ
                MyArray(I, 9) = fndK
                MyArray(I, 10) = fndL
                MyArray(I, 11) = fndM
                I = I + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
        End If
    End With
    'Load data into LISTBOX
   With Me.ListBox1
      .List() = MyArray
   End With
   Worksheets("Main Sheet").Select
End Sub

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi Brian,

Thanks for the reply.

I did try the way you showed me yesterday but the difference with this listbox is that there is a search function first.

I tried all ways but could not adapt your code. The code above (in this post) was done for me a while ago and i thought i would be able to sort headers out for it using yours from yesterday.

However i cant/don't know how to do it. I really hate and for some unknown reason cannot grasp listboxes :oops:

Itried something along the lines of this code and put my row containing the headings as my listrange. Not sure if its correct but it didn't work anyway.

ListBox1.ColumnHeads = True
ListBox1.RowSource = ListRange

Upvote 0
.RowSource is the worksheet range containing the *data* for the list box.
If .ColumnHeads = True then Excel looks for the headings *in the row above the .RowSource*.

As you say, you may not be able to get this to work with your method.

A way of getting around this is to put some Labels at the top of the listbox.
Upvote 0

You need to put the values of array onto sheet somewhere,
'coz header only works when it is from the Range.

and you need to exclude the heading row from the rowsource.

if your heading are in row1, then your rowsourse must begin from row2 like "A2:A10".
Upvote 0
Hi Folks,

Thanks to all of you for your help.

I have managed to solve the problem now. As i said i was hopeless with listboxes so Fat Cat has kindly been giving me a few lessons via e-mail and i think i now understand them a bit better.

So here is the code i got which is working as required:

'This code places the information in the listboxes on the userform
  Application.ScreenUpdating = False
    Dim lastrow As Long
    Dim firstrow As Long
    Dim NumCols As Long
    Dim MyRange As Range
    Dim PRL As Worksheet
        Set PRL = Sheets("Priority List")
        firstrow = 5
        lastrow = PRL.Range("B65536").End(xlUp).Row
        NumCols = 7
        Me.HandPackListBox.ColumnCount = NumCols
        Me.HandPackListBox.ColumnHeads = True
        Me.HandPackListBox.ColumnWidths = "30 pt;60 pt;60 pt;150 pt;60 pt;0 pt;60 pt;"
        Set MyRange = Range(PRL.Cells(firstrow, 1), PRL.Cells(lastrow, NumCols))
        'MsgBox (MyRange.Address) 'Used to test the range
        Me.HandPackListBox.RowSource = MyRange.Address
        Sheets("Main Sheet").Activate

And also this one which is used when i make a choice from the listbox.

Private Sub HandPackListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim PRL As Worksheet
      Set PRL = Sheets("Priority List")
    If Me.HandPackListBox.ListIndex = -1 Then        'Nothing selected from the listbox
        MsgBox " No Selection Has Been Made From The List"
    ElseIf Me.HandPackListBox.ListIndex >= 0 Then    'User has selected something from the listbox
        With Me
            Me.TextBox8.Value = PRL.Cells(HandPackListBox.ListIndex + 5, 2).Value 'the "2" represents the second column in the listbox
            Me.TextBox9.Value = PRL.Cells(HandPackListBox.ListIndex + 5, 3).Value 'the "3" represents the second column in the listbox
            Me.TextBox14.Value = PRL.Cells(HandPackListBox.ListIndex + 5, 4).Value
        End With
End Sub

I am by now means an expert but i need to learn these as they were scaring me, lol.

Thanks again to everyone for their help.
Upvote 0

Forum statistics

Latest member

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
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 "".
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