Cant Get Listbox Headings On Top Row!!

[charllie]

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?

Thanks

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
    Worksheets("Archive").Select
    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
            'c.Select
            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
            Do
                '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

 

[BrianB]

As far as I know can set headers by using the RowSource method.
But I don't have one of the posh new versions of Excel ...

See here :-

http://www.mrexcel.com/board2/viewtopic.php?t=179160&highlight=

 

[charllie]

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

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

Thanks

 

[BrianB]

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

 

[jindon]

Hi

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.

e.g.
if your heading are in row1, then your rowsourse must begin from row2 like "A2:A10".

 

[charllie]

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")
        PRL.Activate
        
        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.