Formating listbox columns

  • Thread starter Thread starter ranswrt
  • Start date Start date
R

ranswrt

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
 
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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
I have one more question. How do I set the horizonl alignment for that
column that has the currency in to the right side?

Bernie Deitrick said:
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 said:
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 said:
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


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

:

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


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
Click to expand...
Click to expand...
Click to expand...
 
AFAIK, alignment is not on a column by column basis.

HTH,
Bernie
MS Excel MVP


ranswrt said:
I have one more question. How do I set the horizonl alignment for that
column that has the currency in to the right side?

Bernie Deitrick said:
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 said:
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
:

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


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

:

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


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
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your help

Bernie Deitrick said:
AFAIK, alignment is not on a column by column basis.

HTH,
Bernie
MS Excel MVP


ranswrt said:
I have one more question. How do I set the horizonl alignment for that
column that has the currency in to the right side?

Bernie Deitrick said:
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


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
:

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


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

:

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


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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top