Search code examples
excelvbauserform

VBA: How to Space Items evenly in a Userform List?

For a starter, I have the following in a loop:

lstResults.AddItem Range("A" & row).value & vbTab & _
                   Range("B" & row).value & vbTab & _
                   Range("C" & row).value

I want to replace vbTab with some other function that will give me consistent spacing, regardless the width of character. My results need to look something like:

Title1       Title2       Title3
12           34           56
Solution

  • Don't loop through your source range, put it into a 2D variant array instead:

    Dim source As Variant
source = Range("A1:C10").Value ' assuming your loop boundaries here.. adjust accordingly

    Then, configure the listbox columns:

    lstResults.ColumnCount = 3
lstResults.ColumnWidths = "50,50,50" ' adjust as you see fit
lstResults.ColumnHeads = True

    Now fill the list with the array:

    lstResults.List = source

    Done :)