Solved Column headers in Listboxes

[Teggun]

Hi guys, I'm trying to add a specific name to the column headers on a value listbox created manually on VBA, and can't find out how to add the tittles I want on each column. Everything works perceftly, I just want to add the column head names on the listbox.
This how the code is structured:

While not rs.EOF
    strItem = "[" & DLookup("OC", "tblOffers", "IDOffer = " & strOffer) & "] " & DLookup("Description", "tblOffers", "IDOffer = " & strOffer)
    Me.lstOffers.AddItem (strItem & ";" & iLote & ";" & DLookup("Lote", "tblOffers", "IDOffer = " & strOffer))
    rs.MoveNext
Wend

I tryed using something I found in some threads, but this property is not working for me.

me.lstOffers.Column(0).Head = "Whatever"

Thanks in advance for your time and help

 

[MajP]

You need to Set captions on the field at the table level if you want something different than the field names. Then set the columnheads property to true.

ListBox.ColumnHeads property (Access)

Office VBA reference topic

docs.microsoft.com

In otherwords the Field could be empLastName and you can make the caption Employee Last Name

 

[arnelgp]

since there are 3 columns in your listbox, you need to add the title prior to filling it with list items:

'// if this code is in Load Event of the form, then we need to ensure that our listbox is empty
If Me.lstOffers.ListCount > 0 Then
    Dim lngItm As Long
    For lngItm = Me.LstOffers.ListCount -1 To 0 Step -1
        Me.LstOffers.RemoveItem 0
    Next
End If
'// add Column Header here
Me.lstOffers.AddItem "Header1;Header2;Header3"

While not rs.EOF    
    strItem = "[" & DLookup("OC", "tblOffers", "IDOffer = " & strOffer) & "] " & DLookup("Description", "tblOffers", "IDOffer = " & strOffer)        
    Me.lstOffers.AddItem (strItem & ";" & iLote & ";" & DLookup("Lote", "tblOffers", "IDOffer = " & strOffer))    
    rs.MoveNext 
Wend

 

[CJ_London]

your value list would look something like this for a two column listbox

"Heading1","Heading2",1,"sometext",2,"moretext"

and would display as
Heading1...Heading2
1....................sometext
2....................moretext

providing, as maj says, you set the column head property to true

 

[Teggun]

Thanks guys, arnelgp got it, I didn't know headers were treated like normal Items in the listbox.

 

[isladogs]

As an alternative to setting captions at table level, you could use aliases in a query to achieve the same effect

 

[MajP]

This is based on some code I use a lot in custom classes to turn a Table/Query into a value list. As Arnel points out the Column Headers of the value list is the first row. In a table/query it is the Caption or if not Caption it uses the field name. So this converts to the value list and puts your headers into the first row. Function creates custom headers for any listbox.

Public Sub CreateCustomHeaders(TheListBox As Access.ListBox, ParamArray ColumnHeaders() As Variant)
Dim rs As dao.Recordset
Dim strSql As String
Dim fldField As dao.Field
Dim fldCount As Integer
Dim strLstValue As String
Dim intColCount As Integer
Dim intRowCounter As Integer
Dim i As Integer
Dim HeaderCount As Integer
If Not TheListBox.RowSourceType = "Value List" Then
strSql = TheListBox.RowSource
TheListBox.RowSource = ""
Set rs = CurrentDb.OpenRecordset(strSql)
'IF LBOX COLUMN COUNT PROPERTY > ROWSOURCE COLUMNS - ERROR
fldCount = rs.Fields.Count
If TheListBox.ColumnCount > fldCount Then
TheListBox.ColumnCount = fldCount
End If
TheListBox.RowSourceType = "Value List"
End If
For i = 0 To UBound(ColumnHeaders)
HeaderCount = HeaderCount + 1
Next i
If HeaderCount <> TheListBox.ColumnCount Then
MsgBox "Make sure colum count and number of headers are equal", vbInformation
Exit Sub
End If

intColCount = TheListBox.ColumnCount
'Since converting to value list column heads do not work
TheListBox.ColumnHeads = True
For i = 0 To UBound(ColumnHeaders)
strLstValue = strLstValue & "'" & ColumnHeaders(i) & "';"
'Debug.Print strLstValue
Next i
strLstValue = Left(strLstValue, Len(strLstValue) - 1)
TheListBox.addItem strLstValue, 0
strLstValue = ""
If rs Is Nothing Then Exit Sub
Do While Not rs.EOF
For i = 0 To intColCount - 1
strLstValue = strLstValue & "'" & CStr(Nz(rs.Fields(i), " ")) & "';"
Next i

'intRowCounter = intRowCounter + 1
rs.MoveNext
strLstValue = Left(strLstValue, Len(strLstValue) - 1)
TheListBox.addItem (strLstValue)
strLstValue = ""
Loop
End Sub

To use

Private Sub cmdCreate_Click()
mdlCreateCustomHeaders.CreateCustomHeaders Me.lstSort, "ID", "Product Name", "Supplier", "Unit Cost"
End Sub

 

[Deleted member 147267]

Why not just hide the column headers altogether and place labels at the top of the control, then alter the label captions? You would then also have options for formatting

 

[MajP]

Why not just hide the column headers altogether and place labels at the top of the control, then alter the label captions? You would then also have options for formatting

That is normally what I would do. It looks nicer IMO.