How to hide column in multicolumn listbox using VBA

user_3931725 0
Viewed 8532

I am using a 2-dimensional array to load data into a multi-column List Box.

I would like to hide a specific column, but don't know how. I can't just exclude the data — because I want to reference it later as a hidden column — but I don't want the user to see it.

Here is what I have so far:

For x = 0 To UBound(ReturnArray, 2)
NISSLIST.ListBox1.Clear 'Make sure the Listbox is empty
NISSLIST.ListBox1.ColumnCount = UBound(ReturnArray, 1) 'Set the number of columns
'Fill the Listbox
NISSLIST.ListBox1.AddItem x 'Additem creates a new row
For y = 0 To UBound(ReturnArray, 1)
    NISSLIST.ListBox1.LIST(x, y) = ReturnArray(y, x) 'List(x,y) X is the row number, Y the column number
    If y = 3 Then 'Want to hide this column in listbox
         NISSLIST.ListBox1.NOIDEA '<<< HELP HERE <<<, What do I put to hide this column of my multi-column listbox????
    End If
   Next y
 Next x
vba excel listbox arrays
2 Answers

The ColumnHidden property only applies to RowSource queries and will not include that column in the listbox values. If you want the values to still be in the listbox and be hidden, the only way to do so in VBA is through the ColumnWidths property.

To hide the 4th column (index 3) you would put the following code:

NISSLIST.ListBox1.ColumnWidths = (";;;0cm")

Or if you want it in a loop like the one in the question:

Dim strWidths As String
For y = 0 To UBound(ReturnArray, 1)
    If y = 3 Then
        strWidths = strWidths + "0cm;"
    Else
        strWidths = strWidths + ";"
    End If
Next y
NISSLIST.ListBox1.ColumnWidths = (strWidths)

Though I would not suggest doing this in a nested loop as it only needs to be done once.

The widths of the other columns will be equally divided unless you specify a width.

I know this is likely no longer useful to the original poster but it may help others (like me) who still run into this issue. It is quite useful for hiding the Key column for when you want users to be able to manipulate data in the listbox.

user_107242670
Related