CODEWITHSUNDEEP
excelvstoexcel-formulaexcel-2010vba
dragonfly
dragonfly

Reputation: 17783

Apply percentage format with VSTO - multiply by 100 behind the scenes

I have a piece of code that formats cell according to requirements:

        Range.Font.Italic = Microsoft.Office.Core.MsoTriState.msoTrue;
        Range.HorizontalAlignment = XlHAlign.xlHAlignGeneral;
        Range.NumberFormat = "0.0_%_);(0.0)_%;-_%_)";

And this code is invoked then button on custom ribbon is pressed. It's similar to percentage cell format. One more thing I have to add is multiplying the cell value by 100. For instance

However, if I do something like this:

        decimal result = Convert.ToDecimal(cell.Value);
        cell.Value = result * 100;

and user hits button multiple times, value is multiplied every time. Is there a way to specify something like display format, so that actual value is preserved and only displayed value is multiplied by 100? Or another way to prevent value from being multiplied multiple times?

Upvotes: 1

Views: 896

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149325

Well you don't need to multiply it by 100

If the cell has .15 then apply the formatting

Range.NumberFormat = "0.00%"

It will automatically change to 15.00 % and you don't need to multiply it by 100.

FOLLOW UP

An out of the box thinking... Why not hide the % symbol by setting the color of the % to white?

VBA CODE

Sub HidePercentage()
    Dim Temp As String

    Temp = Format(ActiveCell.Value, "0.00%")

    With ActiveCell
        .NumberFormat = "@"
        .HorizontalAlignment = xlRight
        .Formula = CStr(Temp)
        .Characters(Start:=Len(Temp), Length:=1).Font.ColorIndex = 2
    End With
End Sub

SNAPSHOT

enter image description here

Upvotes: 1

Brijesh Mishra
Brijesh Mishra

Reputation: 2748

A dirty way would be maintain a hidden sheet, Move original value, or maintain flag for that cell in hidden sheet on button click

Upvotes: 0

Related Questions