Alignment and format in listbox columns

Hi

I need to control the alignment and format of colums in a listbox that i
fill using array.

I need columns 1, 4,5,6 and 7 to be right aligned
I need columns 1, 4, 5 and 6 to be formatted like this #.###,##
i've done like this with column 1 in the code: MyArray(Rk, 0) =
Format(Selection.Value, "##,#0.00")

I need column 7 formatted as percentage.

See the attached picture.

Hoping for help.

Claus Persson

the listbox control provided in both the forms toolbar and control toolbox
toolbar do not offer control of column alignment or numberformatting. When
you load the array, you need to load it with a formatted string. You would
need to simulate alignment with embedded spaces.

--
Regards,
Tom Ogilvy

"Claus" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> I need to control the alignment and format of colums in a listbox that i
> fill using array.
>
> I need columns 1, 4,5,6 and 7 to be right aligned
> I need columns 1, 4, 5 and 6 to be formatted like this #.###,##
> i've done like this with column 1 in the code: MyArray(Rk, 0) =
> Format(Selection.Value, "##,#0.00")
>
> I need column 7 formatted as percentage.
>
> See the attached picture.
>
> Hoping for help.
>
> Claus Persson
>
>
>

Further to Tom I would add that the ListView control does add alignment
functionality along with a bunch of other things the listbox control lacks.
I recently 'discovered' it reading 'Professional Excel Development' by
Stephen Bullen et al. It seems that all machines with recent Office
versions installed will have this control available. It has to be added to
VB Toolbox palette since it's not part of VBA proper.

If you'd like a truly simple example workbook let me know.


--
Jim
"Claus" <[email protected]> wrote in message
news:[email protected]...
| Hi
|
| I need to control the alignment and format of colums in a listbox that i
| fill using array.
|
| I need columns 1, 4,5,6 and 7 to be right aligned
| I need columns 1, 4, 5 and 6 to be formatted like this #.###,##
| i've done like this with column 1 in the code: MyArray(Rk, 0) =
| Format(Selection.Value, "##,#0.00")
|
| I need column 7 formatted as percentage.
|
| See the attached picture.
|
| Hoping for help.
|
| Claus Persson
|
|
|

Hi Jim

You would really help me with a simple example.

I've loaded the ListView into the toolbox and inserted it, but i don't see
how to control alignment for multiple columns.

Regards,

Claus



"Jim Rech" <[email protected]> skrev i en meddelelse
news:%[email protected]...
> Further to Tom I would add that the ListView control does add alignment
> functionality along with a bunch of other things the listbox control
> lacks.
> I recently 'discovered' it reading 'Professional Excel Development' by
> Stephen Bullen et al. It seems that all machines with recent Office
> versions installed will have this control available. It has to be added
> to
> VB Toolbox palette since it's not part of VBA proper.
>
> If you'd like a truly simple example workbook let me know.
>
>
> --
> Jim
> "Claus" <[email protected]> wrote in message
> news:[email protected]...
> | Hi
> |
> | I need to control the alignment and format of colums in a listbox that i
> | fill using array.
> |
> | I need columns 1, 4,5,6 and 7 to be right aligned
> | I need columns 1, 4, 5 and 6 to be formatted like this #.###,##
> | i've done like this with column 1 in the code: MyArray(Rk, 0) =
> | Format(Selection.Value, "##,#0.00")
> |
> | I need column 7 formatted as percentage.
> |
> | See the attached picture.
> |
> | Hoping for help.
> |
> | Claus Persson
> |
> |
> |
>
>

Alignment is set when you create the column header.

LV.ColumnHeaders.Add 2, , "Col2", 70, 2

The last "2" makes this column center aligned. The only documentation I
know of is on MSDN:

http://msdn.microsoft.com/library/de...umnheaders.asp


--
Jim
"Claus" <[email protected]> wrote in message
news:[email protected]...
| Hi Jim
|
| You would really help me with a simple example.
|
| I've loaded the ListView into the toolbox and inserted it, but i don't see
| how to control alignment for multiple columns.
|
| Regards,
|
| Claus
|
|
|
| "Jim Rech" <[email protected]> skrev i en meddelelse
| news:%[email protected]...
| > Further to Tom I would add that the ListView control does add alignment
| > functionality along with a bunch of other things the listbox control
| > lacks.
| > I recently 'discovered' it reading 'Professional Excel Development' by
| > Stephen Bullen et al. It seems that all machines with recent Office
| > versions installed will have this control available. It has to be added
| > to
| > VB Toolbox palette since it's not part of VBA proper.
| >
| > If you'd like a truly simple example workbook let me know.
| >
| >
| > --
| > Jim
| > "Claus" <[email protected]> wrote in message
| > news:[email protected]...
| > | Hi
| > |
| > | I need to control the alignment and format of colums in a listbox that
i
| > | fill using array.
| > |
| > | I need columns 1, 4,5,6 and 7 to be right aligned
| > | I need columns 1, 4, 5 and 6 to be formatted like this #.###,##
| > | i've done like this with column 1 in the code: MyArray(Rk, 0) =
| > | Format(Selection.Value, "##,#0.00")
| > |
| > | I need column 7 formatted as percentage.
| > |
| > | See the attached picture.
| > |
| > | Hoping for help.
| > |
| > | Claus Persson
| > |
| > |
| > |
| >
| >
|
|

Hi Jim

When using:
UserForm1.ListViewCtl1.ColumnHeaders.Add 2, , "Col2", 70, 2

It get the Run-time error 438:
Object doesn't support this property or method.

I use Excel 2002 SP3, with VBA 6.3.

Regards,
Claus

"Jim Rech" <[email protected]> skrev i en meddelelse
news:[email protected]...
> Alignment is set when you create the column header.
>
> LV.ColumnHeaders.Add 2, , "Col2", 70, 2
>
> The last "2" makes this column center aligned. The only documentation I
> know of is on MSDN:
>
> http://msdn.microsoft.com/library/de...umnheaders.asp
>
>
> --
> Jim
> "Claus" <[email protected]> wrote in message
> news:[email protected]...
> | Hi Jim
> |
> | You would really help me with a simple example.
> |
> | I've loaded the ListView into the toolbox and inserted it, but i don't
> see
> | how to control alignment for multiple columns.
> |
> | Regards,
> |
> | Claus
> |
> |
> |
> | "Jim Rech" <[email protected]> skrev i en meddelelse
> | news:%[email protected]...
> | > Further to Tom I would add that the ListView control does add
> alignment
> | > functionality along with a bunch of other things the listbox control
> | > lacks.
> | > I recently 'discovered' it reading 'Professional Excel Development' by
> | > Stephen Bullen et al. It seems that all machines with recent Office
> | > versions installed will have this control available. It has to be
> added
> | > to
> | > VB Toolbox palette since it's not part of VBA proper.
> | >
> | > If you'd like a truly simple example workbook let me know.
> | >
> | >
> | > --
> | > Jim
> | > "Claus" <[email protected]> wrote in message
> | > news:[email protected]...
> | > | Hi
> | > |
> | > | I need to control the alignment and format of colums in a listbox
> that
> i
> | > | fill using array.
> | > |
> | > | I need columns 1, 4,5,6 and 7 to be right aligned
> | > | I need columns 1, 4, 5 and 6 to be formatted like this #.###,##
> | > | i've done like this with column 1 in the code: MyArray(Rk, 0) =
> | > | Format(Selection.Value, "##,#0.00")
> | > |
> | > | I need column 7 formatted as percentage.
> | > |
> | > | See the attached picture.
> | > |
> | > | Hoping for help.
> | > |
> | > | Claus Persson
> | > |
> | > |
> | > |
> | >
> | >
> |
> |
>
>

I tried to email you my simple example file but Hotmail said your address
wasn't valid


--
Jim
"Claus" <[email protected]> wrote in message
news:%[email protected]...
| Hi Jim
|
| When using:
| UserForm1.ListViewCtl1.ColumnHeaders.Add 2, , "Col2", 70, 2
|
| It get the Run-time error 438:
| Object doesn't support this property or method.
|
| I use Excel 2002 SP3, with VBA 6.3.
|
| Regards,
| Claus
|
| "Jim Rech" <[email protected]> skrev i en meddelelse
| news:[email protected]...
| > Alignment is set when you create the column header.
| >
| > LV.ColumnHeaders.Add 2, , "Col2", 70, 2
| >
| > The last "2" makes this column center aligned. The only documentation I
| > know of is on MSDN:
| >
| >
http://msdn.microsoft.com/library/de...umnheaders.asp
| >
| >
| > --
| > Jim
| > "Claus" <[email protected]> wrote in message
| > news:[email protected]...
| > | Hi Jim
| > |
| > | You would really help me with a simple example.
| > |
| > | I've loaded the ListView into the toolbox and inserted it, but i don't
| > see
| > | how to control alignment for multiple columns.
| > |
| > | Regards,
| > |
| > | Claus
| > |
| > |
| > |
| > | "Jim Rech" <[email protected]> skrev i en meddelelse
| > | news:%[email protected]...
| > | > Further to Tom I would add that the ListView control does add
| > alignment
| > | > functionality along with a bunch of other things the listbox control
| > | > lacks.
| > | > I recently 'discovered' it reading 'Professional Excel Development'
by
| > | > Stephen Bullen et al. It seems that all machines with recent Office
| > | > versions installed will have this control available. It has to be
| > added
| > | > to
| > | > VB Toolbox palette since it's not part of VBA proper.
| > | >
| > | > If you'd like a truly simple example workbook let me know.
| > | >
| > | >
| > | > --
| > | > Jim
| > | > "Claus" <[email protected]> wrote in message
| > | > news:[email protected]...
| > | > | Hi
| > | > |
| > | > | I need to control the alignment and format of colums in a listbox
| > that
| > i
| > | > | fill using array.
| > | > |
| > | > | I need columns 1, 4,5,6 and 7 to be right aligned
| > | > | I need columns 1, 4, 5 and 6 to be formatted like this #.###,##
| > | > | i've done like this with column 1 in the code: MyArray(Rk, 0) =
| > | > | Format(Selection.Value, "##,#0.00")
| > | > |
| > | > | I need column 7 formatted as percentage.
| > | > |
| > | > | See the attached picture.
| > | > |
| > | > | Hoping for help.
| > | > |
| > | > | Claus Persson
| > | > |
| > | > |
| > | > |
| > | >
| > | >
| > |
| > |
| >
| >
|
|