Solved: Text/Number Format on Listbox

[slamet Harto]

Hi guys,

Can you advise me on how to format a listbox with text or number format.
your reply would be highly appreciate it.

Thank you!

Private Sub UserForm_Initialize()



Dim r As Integer, r1 As Integer, c As Byte, dArr()
With Sheet9.Range("MyData")
For c = 1 To .Columns.Count
ReDim Preserve dArr(1 To .Rows.Count, 1 To c)
For r = 1 To .Rows.Count
dArr(r, c) = .Cells(r, c)

'Question:
'Pls advise me, how to format each column as text and number

'dArr(r, c) = Format(.Cells(r, 9).Text)
'dArr(r, c) = Format(.Cells(r, 11).Value, "#,###")
'dArr(r, c) = Application.Text(.cells(r,15).Value, "0;(00)")
'dArr(r, c) = Application.Text(.cells(r,16).Value, "#,###;(#,###)") '"0;(0)")




Next r
Next c
LBox.ColumnCount = .Columns.Count

End With
With LBox
.Clear
.ColumnWidths = "30;30;30;30;35;05;35;05;120;50;60;60;60,60;60;30;20;60;450"
.List = dArr()
.Selected(1) = True
.ListStyle = fmListStyleOption 'fmListStylePlain
.SpecialEffect = fmSpecialEffectFlat
End With
ReDim dArr(0)
End Sub

[Bob Phillips]

Private Sub UserForm_Initialize()

Dim r As Long, c As Long, dArr()
With Sheet9.Range("MyData")

ReDim Preserve dArr(1 To .Rows.Count, 1 To .Columns.Count - 1)
For r = 1 To .Rows.Count

For c = 1 To .Columns.Count - 4

dArr(r, c) = .Cells(r, c).Text
Next c
Next r

LBox.List = dArr
LBox.ColumnCount = .Columns.Count
End With

With LBox

.Clear
.ColumnWidths = "30;30;30;30;35;05;35;05;120;50;60;60;60,60;60;30;20;60;450"
.List = dArr()
.Selected(1) = True
.ListStyle = fmListStyleOption 'fmListStylePlain
.SpecialEffect = fmSpecialEffectFlat
End With

End Sub

[slamet Harto]

Hi Bob,

Thank you so much.
Best,
Harto