I am adding items to a listbox using an array and the number format in the
column isn't the same as in the worksheet. How do I format the a column in
the listbox for currency and change the horizontal alignment?
Thanks

You need to format the string that you are putting into the list. You could use code like this in
the initialize event: of course, the specific code depends on where you are getting your values.

Private Sub UserForm_Initialize()

'Set the listbox values
UserForm1.ListBox1.List = Array(1.0002, 10.665757,3.22245)

Dim myL As Variant
Dim i As Integer

'Seet the alignment - could also be done in design mode
UserForm1.ListBox1.TextAlign = fmTextAlignCenter

i = 0
For Each myL In UserForm1.ListBox1.List
UserForm1.ListBox1.List(i) = Format(myL, "0.00")
i = i + 1
Next myL

End Sub


HTH,
Bernie
MS Excel MVP


"ranswrt" wrote in message
...
I am adding items to a listbox using an array and the number format in the
column isn't the same as in the worksheet. How do I format the a column in
the listbox for currency and change the horizontal alignment?
Thanks

How do I do that for a specific column in the listbox?

"Bernie Deitrick" wrote:

You need to format the string that you are putting into the list. You could use code like this in
the initialize event: of course, the specific code depends on where you are getting your values.

Private Sub UserForm_Initialize()

'Set the listbox values
UserForm1.ListBox1.List = Array(1.0002, 10.665757,3.22245)

Dim myL As Variant
Dim i As Integer

'Seet the alignment - could also be done in design mode
UserForm1.ListBox1.TextAlign = fmTextAlignCenter

i = 0
For Each myL In UserForm1.ListBox1.List
UserForm1.ListBox1.List(i) = Format(myL, "0.00")
i = i + 1
Next myL

End Sub


HTH,
Bernie
MS Excel MVP


"ranswrt" wrote in message
...
I am adding items to a listbox using an array and the number format in the
column isn't the same as in the worksheet. How do I format the a column in
the listbox for currency and change the horizontal alignment?
Thanks

This works for the second column, for example, of an at least 2 column listbox.

Private Sub UserForm_Initialize()
'Code to set value
Dim i As Integer

For i = LBound(UserForm1.ListBox1.List, 1) To UBound(UserForm1.ListBox1.List, 1)
UserForm1.ListBox1.List(i, 1) = Format(UserForm1.ListBox1.List(i, 1), "0.00")
Next i

End Sub


HTH,
Bernie
MS Excel MVP


"ranswrt" wrote in message
...
How do I do that for a specific column in the listbox?

"Bernie Deitrick" wrote:

You need to format the string that you are putting into the list. You could use code like this
in
the initialize event: of course, the specific code depends on where you are getting your values.

Private Sub UserForm_Initialize()

'Set the listbox values
UserForm1.ListBox1.List = Array(1.0002, 10.665757,3.22245)

Dim myL As Variant
Dim i As Integer

'Seet the alignment - could also be done in design mode
UserForm1.ListBox1.TextAlign = fmTextAlignCenter

i = 0
For Each myL In UserForm1.ListBox1.List
UserForm1.ListBox1.List(i) = Format(myL, "0.00")
i = i + 1
Next myL

End Sub


HTH,
Bernie
MS Excel MVP


"ranswrt" wrote in message
...
I am adding items to a listbox using an array and the number format in the
column isn't the same as in the worksheet. How do I format the a column in
the listbox for currency and change the horizontal alignment?
Thanks

I modified your code to try to match my spread sheet:
For i = LBound(ChangeOrder.ListBox1.List, 1) To
UBound(ChangeOrder.ListBox1.List, 1)
ChangeOrder.ListBox1.List(i, 4) = Format(ChangeOrder.ListBox1.List(i,
4), "$#,##0.00;[Red]($#,##0.00)")

Next i

It's not making the negative numbers red and I would like it to align to the
right side. How Can I do this?
Thanks
"Bernie Deitrick" wrote:

This works for the second column, for example, of an at least 2 column listbox.

Private Sub UserForm_Initialize()
'Code to set value
Dim i As Integer

For i = LBound(UserForm1.ListBox1.List, 1) To UBound(UserForm1.ListBox1.List, 1)
UserForm1.ListBox1.List(i, 1) = Format(UserForm1.ListBox1.List(i, 1), "0.00")
Next i

End Sub


HTH,
Bernie
MS Excel MVP


"ranswrt" wrote in message
...
How do I do that for a specific column in the listbox?

"Bernie Deitrick" wrote:

You need to format the string that you are putting into the list. You could use code like this
in
the initialize event: of course, the specific code depends on where you are getting your values.

Private Sub UserForm_Initialize()

'Set the listbox values
UserForm1.ListBox1.List = Array(1.0002, 10.665757,3.22245)

Dim myL As Variant
Dim i As Integer

'Seet the alignment - could also be done in design mode
UserForm1.ListBox1.TextAlign = fmTextAlignCenter

i = 0
For Each myL In UserForm1.ListBox1.List
UserForm1.ListBox1.List(i) = Format(myL, "0.00")
i = i + 1
Next myL

End Sub


HTH,
Bernie
MS Excel MVP


"ranswrt" wrote in message
...
I am adding items to a listbox using an array and the number format in the
column isn't the same as in the worksheet. How do I format the a column in
the listbox for currency and change the horizontal alignment?
Thanks

You cannot individually color items in a listbox list, AFAIK. The number format that you are trying
to apply only works for range objects.

HTH,
Bernie
MS Excel MVP


"ranswrt" wrote in message
...
I modified your code to try to match my spread sheet:
For i = LBound(ChangeOrder.ListBox1.List, 1) To
UBound(ChangeOrder.ListBox1.List, 1)
ChangeOrder.ListBox1.List(i, 4) = Format(ChangeOrder.ListBox1.List(i,
4), "$#,##0.00;[Red]($#,##0.00)")

Next i

It's not making the negative numbers red and I would like it to align to the
right side. How Can I do this?
Thanks
"Bernie Deitrick" wrote:

This works for the second column, for example, of an at least 2 column listbox.

Private Sub UserForm_Initialize()
'Code to set value
Dim i As Integer

For i = LBound(UserForm1.ListBox1.List, 1) To UBound(UserForm1.ListBox1.List, 1)
UserForm1.ListBox1.List(i, 1) = Format(UserForm1.ListBox1.List(i, 1), "0.00")
Next i

End Sub


HTH,
Bernie
MS Excel MVP


"ranswrt" wrote in message
...
How do I do that for a specific column in the listbox?

"Bernie Deitrick" wrote:

You need to format the string that you are putting into the list. You could use code like
this
in
the initialize event: of course, the specific code depends on where you are getting your
values.

Private Sub UserForm_Initialize()

'Set the listbox values
UserForm1.ListBox1.List = Array(1.0002, 10.665757,3.22245)

Dim myL As Variant
Dim i As Integer

'Seet the alignment - could also be done in design mode
UserForm1.ListBox1.TextAlign = fmTextAlignCenter

i = 0
For Each myL In UserForm1.ListBox1.List
UserForm1.ListBox1.List(i) = Format(myL, "0.00")
i = i + 1
Next myL

End Sub


HTH,
Bernie
MS Excel MVP


"ranswrt" wrote in message
...
I am adding items to a listbox using an array and the number format in the
column isn't the same as in the worksheet. How do I format the a column in
the listbox for currency and change the horizontal alignment?
Thanks