List Box help

[dave8]

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]

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]

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.

 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]

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]

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?