Excel/Visual Studio/C#. How to change the display name of a cell but keep the formula

Question

Hi there,

I have some code that those:

 void mActiveSheet_Change(Microsoft.Office.Interop.Excel.Range Target)
    {
           if (Target.Cells.Formula.ToString().StartsWith("=FR("))
                   ....
    }

So whenever someone uses my custom function "=FR" I can handle it.

That's fine but while the formula value for that cell is fine, the display value is "#NAME?"

I presume that's because Excel does not recognise what =FR is.

Is there a way that I can change the display name but not the formula so I can have something other then "#NAME?" displayed?

I tried Target.Cells.Value2 = "Boo"; but that also changes the formula

Answer 1

You are correct about #NAME?. That is the Excel error code that is displayed when a function is not recognized. You can't suppress this.

One of the 'dirty tricks' that might work is to hide the message. Detect the background color of the cell, and change the font color to match the background. Unless the cell is highlighted (not just selected) this, in effect, hides the #NAME? error message. After you handle your =FR function and return a result, then you can reset the font color to what it was before so the answer can be seen.

Answer 2

probably you should read this post. might be help you out...

Read me

Forum Article

Answer 3

I think you got this upside down.

The real goal is not to hide #NAME? error; The goal is to create your own function which can be plugged into Excel as any other standard function, like SQRT() or ABS().

Search Google or MSDN for user defined function Excel C# - there are working examples.