Combo Box Fill in Header

[JarekM]

Hi, I have a problem with my combo box. In the screenshot that I have provided, you can see "Contact Person", "Basic Wage" and "Time Wage". I want these items to fill the header in the combo box. Can someone help me? Thank you.

Here is the code that I use:

Public Sub cmFillWorker1(cmName As Object)
    Set WbDb = Workbooks("DataBase.xlsx")
    Set ShDb = WbDb.Sheets("TbVendors")
'    Set WbCd = ThisWorkbook
'    Set shCd = WbCd.Sheets("shSummary")

'----------------------------------------------
    'load CombBox4 VENDOR NAME mine Setup  Unit
    Dim rowStart
    With cmName
        rowStart = 6
        .Clear
        Do Until ShDb.Cells(rowStart, "d") = ""
        .ColumnHeads = True
            .columnCount = 4
            .ColumnWidths = "106;84;48;0"
            .AddItem
            .list(.ListCount - 1, 0) = ShDb.Cells(rowStart, "d")
            .list(.ListCount - 1, 1) = ShDb.Cells(rowStart, "ac")
            .list(.ListCount - 1, 2) = ShDb.Cells(rowStart, "ad")
'            .list(.ListCount - 1, 3) = sheet_Setup.Cells(rowStart, "bf")
'            .list(.ListCount - 1, 1) = sheet_Setup.Cells(rowStart, "bd")
            rowStart = rowStart + 1
        Loop
    End With
'----------------------------------------------

    '    WbDb.Close
    Set WbDb = Nothing
    Set ShDb = Nothing
    Set WbCd = Nothing
End Sub

 

[DanteAmor]

Hi @JarekM.

Thanks for posting on the board.​

I want these items to fill the header in the combo box

The ListBox only displays column headers if you use RowSource property. Otherwise, they are not available.

--------------------------------​

I show you 3 ways to display headers.
The 3 options is assuming that the headers are in row 6.

--------------------------------​

1. The first option. You are already using it, just remove this instruction:

.ColumnHeads = True

The problem with this option, as you've already seen, is that the headers are in the listbox as an item.​

Example:​

​

--------------------------------
​

2. The second option. Use the headers with the ColumnHeads and RowSource properties.

This way you will be able to see the headers as headers and not as an item:​

​

To achieve the above and noting that the information that you are going to load in the combo is not continuous columns, then you must create an auxiliary sheet.​

​

In the code I put comments for your better understanding:​

Public Sub cmFillWorker1(cmName As MSForms.ComboBox)
  Dim WbDb As Workbook
  Dim shdb As Worksheet, shax As Worksheet
  
  Set WbDb = Workbooks("DataBase.xlsx")
  Set shdb = WbDb.Sheets("TbVendors")
  
  Set shax = WbDb.Sheets("Aux")                           'Auxiliar sheet, The sheet can be hidden
  
  shax.Cells.ClearContents
  shdb.Range("D:D, AC:AC, AD:AD").Copy shax.Range("A1")   'Here you put the columns you need to see in the combobox.
  
  With cmName
    .ColumnHeads = True                                   'enable columnhead
    .ColumnCount = 4
    .ColumnWidths = "106;84;48;0"
    
    'Assuming the headers are in row 6, then the data starts at row 7.
    'Rowsource automatically loads the headers from the previous row.
    .RowSource = shax.Name & "!A7:D" & shax.Range("A" & Rows.Count).End(3).Row
  End With
  
  Set WbDb = Nothing
  Set shdb = Nothing
  Set shax = Nothing
End Sub

--------------------------------
​

3. The third option. It is using labels.

If you don't want to create the auxiliar sheet, then another option is to add the headings in labels:​

​

For above, use this code:​

Public Sub cmFillWorker1(cmName As msforms.ComboBox)
  Dim WbDb As Workbook
  Dim shdb As Worksheet
  Dim rowStart As Long

  Set WbDb = Workbooks("DataBase.xlsx")
  Set shdb = WbDb.Sheets("TbVendors")
  With cmName
  rowStart = 7
  .Clear
  Do Until shdb.Cells(rowStart, "D") = ""
    .ColumnCount = 4
    .ColumnWidths = "106;84;48;0"
    .AddItem
    .List(.ListCount - 1, 0) = shdb.Cells(rowStart, "D")
    .List(.ListCount - 1, 1) = shdb.Cells(rowStart, "AC")
    .List(.ListCount - 1, 2) = shdb.Cells(rowStart, "AD")
    rowStart = rowStart + 1
  Loop
  End With
  Set WbDb = Nothing
  Set shdb = Nothing
End Sub

--------------
Review which is the best option for you.
Cordially
Dante Amor
--------------​

 

[JarekM]

Hi @JarekM.

Thanks for posting on the board.​

The ListBox only displays column headers if you use RowSource property. Otherwise, they are not available.

--------------------------------​

I show you 3 ways to display headers.
The 3 options is assuming that the headers are in row 6.

--------------------------------​

1. The first option. You are already using it, just remove this instruction:

.ColumnHeads = True

The problem with this option, as you've already seen, is that the headers are in the listbox as an item.​

Example:​

​

View attachment 92835​

--------------------------------
​

2. The second option. Use the headers with the ColumnHeads and RowSource properties.

This way you will be able to see the headers as headers and not as an item:​

​

View attachment 92836​

To achieve the above and noting that the information that you are going to load in the combo is not continuous columns, then you must create an auxiliary sheet.​

​

In the code I put comments for your better understanding:​

Public Sub cmFillWorker1(cmName As MSForms.ComboBox)
  Dim WbDb As Workbook
  Dim shdb As Worksheet, shax As Worksheet
 
  Set WbDb = Workbooks("DataBase.xlsx")
  Set shdb = WbDb.Sheets("TbVendors")
 
  Set shax = WbDb.Sheets("Aux")                           'Auxiliar sheet, The sheet can be hidden
 
  shax.Cells.ClearContents
  shdb.Range("D:D, AC:AC, AD:AD").Copy shax.Range("A1")   'Here you put the columns you need to see in the combobox.
 
  With cmName
    .ColumnHeads = True                                   'enable columnhead
    .ColumnCount = 4
    .ColumnWidths = "106;84;48;0"
   
    'Assuming the headers are in row 6, then the data starts at row 7.
    'Rowsource automatically loads the headers from the previous row.
    .RowSource = shax.Name & "!A7:D" & shax.Range("A" & Rows.Count).End(3).Row
  End With
 
  Set WbDb = Nothing
  Set shdb = Nothing
  Set shax = Nothing
End Sub

--------------------------------
​

3. The third option. It is using labels.

If you don't want to create the auxiliar sheet, then another option is to add the headings in labels:​

View attachment 92837​

​

For above, use this code:​

Public Sub cmFillWorker1(cmName As msforms.ComboBox)
  Dim WbDb As Workbook
  Dim shdb As Worksheet
  Dim rowStart As Long

  Set WbDb = Workbooks("DataBase.xlsx")
  Set shdb = WbDb.Sheets("TbVendors")
  With cmName
  rowStart = 7
  .Clear
  Do Until shdb.Cells(rowStart, "D") = ""
    .ColumnCount = 4
    .ColumnWidths = "106;84;48;0"
    .AddItem
    .List(.ListCount - 1, 0) = shdb.Cells(rowStart, "D")
    .List(.ListCount - 1, 1) = shdb.Cells(rowStart, "AC")
    .List(.ListCount - 1, 2) = shdb.Cells(rowStart, "AD")
    rowStart = rowStart + 1
  Loop
  End With
  Set WbDb = Nothing
  Set shdb = Nothing
End Sub

--------------
Review which is the best option for you.
Cordially
Dante Amor
--------------​

Hi Dante, thank you for the quick reply and the several options that you have given me. I found that the second option worked the best for me.