CODEWITHSUNDEEP
vbaexcelformatting
hbabbar
hbabbar

Reputation: 967

How to do conditional formatting of a label in Excel VBA

I need to format a label value conditionally in "$K" and "$M" when the data is in thousands and millions. I've been using the following format which works absolutely fine in Excel cells ($#,##0.0,"K") and ($#,##0.00,,"M") respectively, this doesnt work when I use it to format a label caption using VBA with the following code :

lblInvestmentValue.Caption = Format(CStr(dblInvestmentVal), "[>=1000000] $#,##0.0,,""M"";[>0] $#,##0.0, ""K"";General")

On using this I get the following output

dblInvestmentVal Caption        Expected Caption
812              $812.0M        $812
8280119          $8,280,119.0M  $8.29M
91281            $12,367.0M     $12.4K

Any pointers

Upvotes: 0

Views: 621

Answers (3)

KyokoHunter
KyokoHunter

Reputation: 397

The TEXT worksheet function seems to respect the format you initially specified and you can use it in your VBA by virtue of Application.WorksheetFunction.

Application.WorksheetFunction.Text(812, "[>=1000000] $#,##0.0,,""M"";[>0] $#,##0.0, ""K"";General")

The VBA reference for FORMAT doesn't cover conditional number formatting but it does have a number formatting section, so I expect conditional number formatting isn't valid - although I find it interesting that using it defaults to the first pattern in your specified format.

Upvotes: 0

FunThomas
FunThomas

Reputation: 29592

I am not sure if this conditional number formatting is supported by the format command, but you could easily replace this with:

Function ConditionalFormatNumber(n As Double) As String
    If n > 1000000 Then
        ConditionalFormatNumber = Format(n / 1000000, "$#,##0.00,,""M""")
    ElseIf n > 1000 Then
        ConditionalFormatNumber = Format(n / 1000, "$#,##0.00, ""K""")
    Else
        ConditionalFormatNumber = Format(n, "$#,##0.0")
    End If
End Function

Upvotes: 0

Achim Gmeiner
Achim Gmeiner

Reputation: 151

You could use an IIF statement.

lblInventmentValue.Caption = IIF(Abs(dblInvestmentVal) >= 1000000, Format(dblInvestmentVal / 1000000, "$#,##0.0,,""M"""),IIF(Abs(dblInvestmentVal) >= 1000, Format(dblInvestmentVal / 1000, "$#,##0.0,,""K"""),Format(dblInvestmentVal, "$#,##0.0")))

Statement use Abs(dblInventmentVal) to also format negativ numbers correctly.

Upvotes: 1

Related Questions