Format in Combobox / Listbox

[CobraLAD]

Hi

I have on a sheet data from column A - E

000001 | ACC001 | Account Name | 25 Mar 2008 | R 20,560.00


when I list in a Combobox with 5 columns it display like this

1 | ACC001 | Account Name | 3/25/2008 | 20560

Is there a way to display exactly as it is on the sheet. I am using this code

Dim MainList As Integer
MainList = Sheet5.Range("A65536").End(xlUp).Row
UserForm1.ComboBox1.List = Sheet5.Range("A2:E2", "A" & MainList).Value

I have tried a ListBox as well, same thing

[mikerickson]

The .Text property of the cells, which returns what is displayed rather than the underlying value, is needed. Unfortunatly, it won't bulk load like .Value, so a loading loop is needed.

(Mac OS10.5 Excel 2004)

Dim xArray As Variant
Dim i As Long, j As Long
With Sheet5.Range("A2:E2", "A" & Sheet5.Range("A65536").End(xlUp).Row)
    ReDim xRRay(1 To .Rows.Count, 1 To Columns.Count)
    For i = 1 To .Rows.Count
        For j = 1 To .Columns.Count
            xArray(i, j) = .Cells(i, j).Text
        Next j
    Next i
End With

UserForm1.ListBox1.List = xArray
UserForm1.Show

[jindon]

try

Dim a, i As Long
Dim MainList As Integer
MainList = Sheet5.Range("A65536").End(xlUp).Row
a = Sheet5.Range("A2:E2", "A" & MainList).Value
For i = 1 To UBound(a,1)
    a(i,1) = Format$(a(i,1),"000000")
    a(i,4) = Format$(a(i,4), "dd mmm yyyy")
    a(i,5) = "R " & Format$(a(i,5),"#,##0.00")
Next
UserForm1.ComboBox1.List = a