Formatting the columns in a userform combobox

[SJ-NY]

All:


I have just started to incorporate userforms into my procedures. I have been successful in using a two column combobox.


My question is formatting. The first column is text and the second is numerical. Is it possible to apply different formatting to each column of the combobox? If it is, examples of coding would be great.


I tried posting this earlier today but it does not appear it went through. If this is a duplicate post my apologies.


Thanks for you help


Steve

 

[mikerickson]

The columns of a combobox or a listbox are formatted the same.

But there are work-arounds. How do you want the text column (first column?) to be formatted and how do you want the numeric column to be formatted?

 

[SJ-NY]

Mike
Thank you for you quick response. I will end up with three columns in this order

text
numeric - general, no decimals
percentage

thank you for you help

Steve

 

[mikerickson]

One thing to remember is that all the entries in a combobox (or listbox) are strings.

If you want the numerals in column 2 to appear as percentages, you can use the Format function.
For this test routine, I have names in column A and numbers in column B of the worksheet. This routine puts those values in a two column ListBox.

Dim oneCell As Range

With ListBox1
    .ColumnCount = 2
    .TextColumn = 1
    .BoundColumn = 2
    For Each oneCell In Range("A1:A4")
        .AddItem oneCell.Value
        .List(.ListCount - 1, 1) = Format(oneCell.Offset(0, 1).Value, "0.00 %; -0.00 %")
    Next oneCell
End With

Note that the .BoundColumn = 2 line makes the .Value property of the Listbox equal to the string in the second column.

If you want to calculate with that, it has to be converted from the string "12.3 %" to the number .123. The VBA function Val errors when there is a terminal "%" in its argument so Evaluate is used.

Private Sub ListBox1_Click()
    UserForm1.Caption = Evaluate(ListBox1.Value)
End Sub

I hope this helped.
ListBoxes and ComboBoxes act almost identically, so this code will also work with a ComboBox.

 

[SJ-NY]

MIke:

thank you. I will give this a try and let you know how it works.

Again thank you for your help.

Steve