Formating listbox columns

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
 
B

Bernie Deitrick

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
 
B

Bernie Deitrick

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
 
R

ranswrt

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
 
B

Bernie Deitrick

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...
 
R

ranswrt

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...
 
B

Bernie Deitrick

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...
 
R

ranswrt

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Listbox Column help 2
Listbox search question 4
Setting column width in a table with a macro is not working 0
Excel does not accept text format! 0
Multi listbox change event - selected or deselected 6
User Input to Generate ListBox Range 0
ListBox Remove Certain Text 6
Dump ListBox Contents to Sheet & Stay on Active Sheet 0

Top