Formatting a label caption as "$##,###.##"

[TAPS_MikeDion]

Does anyone know why I cannot get the following code to show the digits after the decimal point in my label caption?

Budget is the label name.

Everything works great except for the fact that I can't get it to display the cents.
1900 displays as "$1,900."
1900.00 displays as "$1,900."

No matter what is entered into the Excel sheet (the cells are also formatted as $##,###.## and it shows up correctly in the sheet) the label caption will not show what is after the decimal point.

Budget = " " & Format(ws.Cells(SelectedRow, 34).Value, "$##,###.##")

Thanks!

 

[DanteAmor]

Try:

Budget = " " & Format(ws.Cells(SelectedRow, 34).Value, "$##,###.[COLOR=#0000ff]00[/COLOR]")

 

[TAPS_MikeDion]

Hi Dante,
Thanks, but using that method only returns 00 for cents. Also, when used with (for example) $1900.50 the result is "$190,0.5.00"

 

[DanteAmor]

That may be because in the cell you have a text, check if in the cell you have a text or a numerical value.

 

[TAPS_MikeDion]

The column is formatted as numbers. It's okay, I figured out a different solution. It's longer, but it works. LOL
Thank you though. The budget for any location will never be high enough to require a hundred thousandths comma, so this morks.

For the record...

Sub BudgetNum()
    
    Dim BgtNum As String
    Dim OrigNum
    Dim ws As Worksheet    
    Set ws = Sheets("DataSheet")
    
    OrigNum = ws.Cells(PayrollInformationForm.ComboBox1.ListIndex + 2, 34).Value
    
    If Len(OrigNum) > 6 Then
        'Greater than $999.99
         BgtNum = " $" & _
        Left(OrigNum, Len(OrigNum) - 6) & _
        "," & Right(OrigNum, 6)
        PayrollInformationForm.Budget = BgtNum
    End If
    
End Sub

 

[DanteAmor]

Ok, the important thing is that it works for you.

 

[TAPS_MikeDion]

What the???
I just saw that I typed "morks" instead of works. Oh well.

 

[DanteAmor]