How to have a listbox display currency

[RJQMAN]

I have a form in my program that the user clicks on in special cases.
The form is asking the user to supply some additional information. As
a reference on the form, I planned to have a Listbox display some data
the user entered before. It works except...

The item displayed for the user to reference is currency. No matter
what I do, I cannot get the listbox on the 2nd form to display the
number (used only for reference for the convenience of the user - not
for any calculations) as currency.

For example. User enters $4,567 in an earlier form. This number is
stored in a cell and is displayed in that formatted cell as $4,567.

The new form appears, with a Listbox at the top of the form. The
listbox draws the number from that cell so the user can see what he or
she entered before, but it displays the total amount as 4567 instead
of $4,567. Is there any way to have the listbox display the amount as
$4,567, or is there any other way (other than using a listbox) that I
can use to draw that number from an Excel cell and display it as
$4,567 instead of 4567?

Thanks in advance.

 

[Dave Peterson]

It doesn't sound like you're using .additem to add to the listbox.

If you use this, you can format the number the way you want:

dim myCell as Range
dim myRng as range

set myrng = worksheets("somesheet").range("somerange")

with me.listbox1
for each mycell in myrng.cells
.additem mycell.text 'if it's formatted nicely
'or
.additem format(mycell.value, "$#,##0.00")
next mycell
end with