excel vba - formating numbers in an array as currency

[scroce]

I'm tryin' to do a little somthin' like:

Me.cboMyCombo.List = Array(0, 25000, 50000, 100000, 250000, 500000, 1000000)

which works great, but I want those numbers to be formatted in currency with the appropriate commas, etc.

I made various attempts using the formatCurrency function, but wasn't able to find a way to do this -

I'm sure i'm overlooking something relatively simple - does anyone know how to do this?


Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...

 

[stickers]

I'm not sure about using combo boxes in excel at all, but this bit of code might help you figure it out - I use it to format a range of cells:

.ActiveSheet.Range(.ActiveSheet.Cells(7, 3), .ActiveSheet.Cells(R, C)).NumberFormat = "$#,##0.00"

 

[scroce]

thanks for the response - I don't think that will work because in a combo box you are not actually formatting cells of the worksheet. - the combo box is a separate object.


Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...

 

[nath]

Hi there,

Try the following:

Me.cboMyCombo.List =Array(Format(0, "£#,##0.00"), Format(25000, "£#,##0.00"), Format(50000, "£#,##0.00"))

Nath

 

[bryanbayfield]

Private Sub UserForm_Initialize()
Dim dollar_array As Variant, i As Integer
dollar_array = Array(0, 25000, 50000, 100000, 250000, 500000, 1000000)
For i = 0 To 6
    Select Case dollar_array(i)
        Case 0
            MyCombo.AddItem dollar_array(i)
        Case Else
            MyCombo.AddItem Format(dollar_array(i), "$##,000")
        End Select
    Next
End Sub

There's a number of ways of doing it but that's the most sensible I can think of.

By the way, if you're initialising the form you don't need to prefix teh name of teh control with

Me.

, in fact I can't remember ever having to use that prefix.

Hope this helps.

 

[scroce]

Thanks for the responses!

I also found this little bit of code in another example that I modified, and it seems to work:

'Dim x
'Dim i As Integer
'x = Array(0, 25000, 50000, 100000, 250000, 500000, 1000000)
'For i = LBound(x) To UBound(x)
'x(i) = FormatCurrency(x(i))
'Next i

'Me.cboMyCombo.List = x

Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...