Formating listbox columns
 

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

Formating listbox columns
 

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

Formating listbox columns
 

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

Formating listbox columns
 

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

Formating listbox columns
 

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

Formating listbox columns
 

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

Formating listbox columns
 

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" wrote:

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

Formating listbox columns
 

AFAIK, alignment is not on a column by column basis.

HTH,
Bernie
MS Excel MVP


"ranswrt" wrote in message
...
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" wrote:

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

Formating listbox columns
 

Thanks for your help

"Bernie Deitrick" wrote:

AFAIK, alignment is not on a column by column basis.

HTH,
Bernie
MS Excel MVP


"ranswrt" wrote in message
...
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" wrote:

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