List Box help

dave8

Active Member
Joined
Jul 8, 2007
Messages
264
I have a listbox on a form which I load values using a loop in VBA. The values are in Sheet1!A1:A25. The property list box is set to ColumnHead = True. This works fine showing the Column headers at the beginning of the list. However, I have another listbox on a worksheet in which the ColumnHead is also set to True. But I don't get the Column Headers. What could be wrong? Of course, I'm using values from another Sheet2!A1:A30. What am I missing?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Are you sure what you are seeing in the first row are actually headers?

Normally the only way to display headers is to set the RowSource property to the range.

If you populate the listbox using other methods then the headers might appear but they'll be regarded as just another item in the list.

For both of the controls can you post the code you are using to populate them?
 

dave8

Active Member
Joined
Jul 8, 2007
Messages
264
This is a partial code that I use to load the listbox. This listbox is on my worksheet. Please note that towards the bottom of the code, I had to comment out the .Rowsource because it errored. I was able to reference the .Rowsource property when the listbox was used on the form, however.

Code:
 Dim Data(1 To 200, 1 To 5) As Variant
  
  For i = 1 To Num
     Data(i, 1) = Worksheets(ws).Range("A" & i)
  Next i
  
  
  'Load last name
  For i = 1 To Num
     Data(i, 2) = Worksheets(ws).Range("B" & i)
  Next i
  
  'Load first name
  For i = 1 To Num
     Data(i, 3) = Worksheets(ws).Range("C" & i)
  Next i
  
  'Load Address
  For i = 1 To Num
     Data(i, 4) = Worksheets(ws).Range("D" & i)
  Next i
  
  'Load Notes
  For i = 1 To Num
     Data(i, 5) = Worksheets(ws).Range("E" & i)
  Next i
  
   
  Sheets("Sheet3").ListBox1.ColumnCount = 5
  Sheets("Sheet3").ListBox1.List = Data
  
   
  ColCnt = 5
  Set rng = ActiveSheet.Range("A2:" & "E" & i)
  With Sheets("Sheet3").ListBox1
        .ColumnCount = ColCnt
     '   .RowSource = rng.Address
        cw = ""
        For c = 1 To .ColumnCount
            cw = cw & rng.Columns(c).Width & ";"
        Next c
        .ColumnWidths = cw
        .ColumnHeads = True
        .ListIndex = 0
    End With
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Dave

You have to use RowSource to get headers wherever the control is located.

The difference here seems to be that you are having trouble referring to the RowSource for the listbox on the worksheet.

What type of control is it? Forms? ActiveX?
 

dave8

Active Member
Joined
Jul 8, 2007
Messages
264
I'm not completely sure if it's forms control or ActiveX. The listbox that I'm using on the Worksheet has a property component and the other does not. So is this forms or ActiveX?? When I add the listbox on the Worksheet, I don't see the Rowsource property but I can see the other property values, such as setting the Columnhead. On the other hand, when I add the listbox on the userform, I do see the Rowsource property. So, if I need to access the Rowsource property on the listbox, why is it not there when I use on the Worksheet?
 

Forum statistics

Threads
1,082,139
Messages
5,363,364
Members
400,731
Latest member
Jackserver

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top