I have a list with values I like to add to combobox in my userform.
The values I want are in Column A and Column Z (so values from 2 columns). I manage to add the values with the AddItem function but struggling to add a header to the dropdown (a few posts said this is not possible).
As alternative I saw ListFillRange but I cannot figure out if this can be used for two columns which are not next to each other. Appreciate the help.
a few posts said this is not possible
I usually do not reply to questions which do not show any efforts but this is an interesting one. I tend to agree with you that lot of people think that you cannot show headers in a ComboBox.
But it is possible to show headers in a Combobox. Here is a demonstration. You will of course have to take help of a helper sheet for this if you do not want to change the original sheet.
TEST CASE
For our demonstration, we will take 2 non-contigous range A1-A5 and D1-A5
LOGIC
CODE
Option Explicit
Dim ws As Worksheet
Private Sub UserForm_Initialize()
Dim wsInput As Worksheet
'~~> Input sheet. Change as applicable
Set wsInput = Sheet1
'~~> Add a new sheet. Hide it (Optional)
Set ws = ThisWorkbook.Sheets.Add
ws.Visible = xlSheetHidden
'~~> Copy the non-contigous range to the new sheet
wsInput.Range("A1:A5").Copy ws.Range("A1")
wsInput.Range("D1:D5").Copy ws.Range("B1")
Dim rng As Range
'~~> Get your range
Set rng = ws.Range("A1:B5")
'~~> Convert range to table
ws.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "MyTable"
'~~> Few combobox settings and we are done
With ComboBox1
.ColumnCount = 2
.ColumnHeads = True
.RowSource = "MyTable"
End With
End Sub
'~~> Delete the temp sheet we created
Private Sub UserForm_Terminate()
Application.DisplayAlerts = False
If Not ws Is Nothing Then ws.Delete
Application.DisplayAlerts = True
End Sub
OUTPUT
ALTERNATIVE
If you are not ok with the idea of helper sheet and are ok to sacrifice on the header part then you can populate a combobox using non contigous ranges. See Excel VBA Multicolumn Listbox add non contiguous range. You will of course have to edit the code to suit your needs. Since there are two columns only, your final array would look like Dim Ar(1 To LastRow, 1 To 2). This array will hold values from both columns.
I use the following code to add headers above listboxes and comboboxes. It seems a bit like a sledgehammer to crack a nut but sometimes the nut has to be cracked, and all the other methods and tools that I have seen also fall into the category of sledgehammer.
To make this as simple as possible for myself I have defined a class called clsListBoxHeaders and I include that code below. Then suppose you have a ListBox with 3 columns you need to
To do this insert the following code in your user form
Dim lbHeaders As New clsListBoxHeaders
Set lbHeaders.ListBox = ListBox1
lbHeaders.Headers = "First Header;Second Header;Third Header"
lbHeaders.ColumnWidths = "40;50;60"
Note that the number of headers and the number of columnwidths must match exactly the number of columns in your listbox/combobox
To clear the header data use:
lbHeaders.Clear
If you want to format the labels (e.g. font) then you can access the labels as a variant array
lbHeaders.Labels
The class module code is as follows:
Option Explicit
' clsListBoxHeaders - Display header info above a ListBox or ComboBox
' To use this class in your project:
' Add a class module called clsListBoxHeaders and paste this code into it
' For each ListBox or ComboBox for which you wish to display column headers insert the following code in your userform:
' Dim lbHeaders As New clsListBoxHeaders
' Set lbHeaders.ListBox = ListBox1
' lbHeaders.Headers = "First Header;Second Header;Third Header"
' lbHeaders.ColumnWidths = "40;50;60"
'Note that the number of headers and the number of columnwidths must match exactly the number of columns in your listbox/combobox
' To clear the header data use:
' lbHeaders.Clear
Const LabelHeight As Integer = 10 ' Height of the header labels.
Const LabelOffset As Integer = 10 ' Offset to get the header to align correctly to first column in listbox
Private myListBox As Object
Private myParent As Object
Private lblHeaders() As MSForms.Label
Private sColumnWidths() As Single
Public Property Set ListBox(ListBox As Object)
Set myListBox = ListBox
Set myParent = ListBox.Parent
End Property
Public Property Let Headers(sHeaders As String)
Dim lLeft As Long, vHeaders As Variant
Dim iCol As Integer
With myListBox
vHeaders = Split(sHeaders, ";")
ReDim lblHeaders(.ColumnCount)
If UBound(sColumnWidths) = 0 Then
ReDim sColumnWidths(.ColumnCount)
For iCol = 1 To .ColumnCount
sColumnWidths(iCol) = .Width / .ColumnCount
Next
End If
lLeft = LabelOffset
For iCol = 1 To .ColumnCount
Set lblHeaders(iCol) = myParent.Controls.Add("Forms.Label.1")
With lblHeaders(iCol)
.Top = myListBox.Top - LabelHeight
.Left = lLeft + myListBox.Left
.Width = sColumnWidths(iCol)
.Height = LabelHeight
lLeft = lLeft + sColumnWidths(iCol)
.Visible = True
.Caption = vHeaders(iCol - 1)
.ZOrder fmZOrderFront
End With
Next
End With
End Property
Public Property Let ColumnWidths(ColumnWidths As String)
Dim vSplit As Variant
Dim lLeft As Long
Dim iCol As Integer
With myListBox
vSplit = Split(ColumnWidths, ";")
ReDim sColumnWidths(.ColumnCount)
For iCol = 1 To .ColumnCount
sColumnWidths(iCol) = vSplit(iCol - 1)
Next
lLeft = LabelOffset
If UBound(lblHeaders) > 0 Then
For iCol = 1 To .ColumnCount
With lblHeaders(iCol)
.Left = myListBox.Left + lLeft
.Width = sColumnWidths(iCol)
lLeft = lLeft + sColumnWidths(iCol) ' + LabelOffset
End With
Next
End If
End With
End Property
Public Property Get Labels() As Variant
Dim iCol As Integer
Dim vLabels As Variant
With myListBox
ReDim vLabels(.ColumnCount - 1)
For iCol = 1 To .ColumnCount
Set vLabels(iCol - 1) = lblHeaders(iCol)
Next
End With
Labels = vLabels
End Property
Public Sub Clear()
Dim i As Integer
For i = 1 To UBound(lblHeaders)
myParent.Controls.Remove lblHeaders(i).Name
Next
Class_Initialize
End Sub
Private Sub Class_Initialize()
ReDim lblHeaders(0)
ReDim sColumnWidths(0)
End Sub
