Currency Format in a Listbox Column

General
1

Hi all!

How can I have a Currency format in a specific column of a Listbox?
Regards. :smiley:

2

read up on the CellTextPaint event and the Format function

3

Now I am Using CellTextPaint on the Listbox.

I can change the color of the text based on certain criteria, but I can’t format the content of the cell as Currency, when I redraw the table, it changes to “$0.00”, This is the Code that I’m using:

if Column = 9 then
Dim dbl As double
dbl = val(me.Cell(Row,Column))
me.Cell(Row,Column) = format(dbl,“\$###,##0.00”)
end

What Am I doing Wrong? :frowning:

4

I would say that me.cell(row,column) does not contain a number

5

No, It contains a Number, that is loaded from a Recordset.

This Recordset was filled with a mySQL Table. this field has type: DECIMAL(0,0).
If you are saying that it can’t see a number to format.

But If I display it in a Message Box, it works, returning me the number formatted to currency:
Msgbox format(dbl,“\$###,##0.00”)

6 
if Column = 9 then
Dim dbl As double
dbl = val(me.Cell(Row,Column))
me.Cell(Row,Column) = format(dbl,"\\$###,##0.00")
end

you are over-writing yourself

1st time… it is probably correct… .changing 2.53 to “$2.53”
but next time it refreshes it is trying to convert “$2.53” with IS NOT A NUMBER and you get “$0.00”

store you database value in the ROWTAG or CELLTAG , this way the original database value is never altered by the listbox

and try this

if Column = 9 then
Dim dbl As double
dbl = val(me.CellTAG(Row,Column)) //<--- NOTICE CELLTAG not CELL
me.Cell(Row,Column) = format(dbl,"\\$###,##0.00")
end
7

A Mysql field with type DECIMAL(0,0) can hold a value? Without a number before and after the decimal point?

8

Its DECIMAL(15,2)

9

It works!, Is it Celltag a Kind of Cell ID?, O How it Works? Thanks

10

I format the cell at the time of the populate the listbox, in a method, without using the celltextpaint as well:

 lb.Cell (lb.LastIndex, 4) = Format (Val (lb.Cell (lb.LastIndex, 4)), "\\ R \\ $ \\ ###, ###, ## 0.00")

… and work’s fine.

11

each cell in a Listbox can actually hold TWO values CELL(row,col) and CELLTAG(row,col)
and each ROW can also have a ROWTAG(row)

12

[quote=195467:@Adelar Duarte]I format the cell at the time of the populate the listbox, in a method, without using the celltextpaint as well:

 lb.Cell (lb.LastIndex, 4) = Format (Val (lb.Cell (lb.LastIndex, 4)), "\\ R \\ $ \\ ###, ###, ## 0.00")

… and work’s fine.[/quote]
I would store the actual value in the CellTag property and use the Cell property for the display only.

That gives you the best of all worlds. If you need the value you can just get it from the CellTag property yet the user will see the properly formatted number in the actual cell. The reason is that it is difficult to return the actual value from a formatted string, especially if it looks like $2,501.74. As a string you cannot just Val() that.

This method also gives you the opportunity of using the standard populate case of setting the Cell at load time or using the CellTextPaint event.

Simon.

13

Setting the columnalignment to Decimal & the columnalignmentoffset to -20 also does a good job.

14

I’ve done this in CellTextPaint event of the Listbox and it orders at these way:

The column where appears thats currency values is th number 9

so I write on CellTextpaint Listbox’s event:

Select Case Column Case 9 me.ColumnAlignment(9)= me.AlignDecimal me.ColumnAlignmentOffset(9) = -20 End Select

15.00

1652.23
129.99
1.26
10000.52
200.52
2698.77
2.69

instead of:
1.26
2.69
15.00
129.99
200.52
1652.23
2698.77
10000.52

Am I omitting something?

15

Try moving the code to the open event of the listbox.

16

You need to set ColumnAlignment and ColumnAlignmentOffset just once (in the open event) of the listbox.

17

I also do that And Keep doing the same

18

Don’t have anything in the celltextpaint event handler, just the open event handler.

19

You can’t sort the string representation. Store the original value as a double and use that in CompareRows to handle sorting the column.

20

[quote=275332:@Wayne Golding]Don’t have anything in the celltextpaint event handler, just the open event handler.[/quote]Sure, I don’t have nothing on Celltextpaint event I erased all code in this method. and Not working.