Conditional Formatting Fails with Workbooks That are Opened from the Addin

[Nick VG]

Hi,

This is how I produce the problem:

- I have an ExcelFunction that returns a bool for whether conditional formatting should apply to a cell. Let's say it always returns true, i.e. that conditional formatting should always apply:

   [ExcelFunction]

   public static object TestFunc() {

      return true;

   }

- I have a ribbon Event that opens a workbook when pressed:

   public void OnButtonPressed(IRibbonControl control) {

      var app = (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;   

      app.Workbooks.Open(@"C:\Temp\TestBook.xlsx");

   }

- The spreadsheet, TestBook.xlsx, has a name, TestName, with a RefersTo of "=TestFunc()", and a cell with a conditional formatting rule with the formula "=TestName".

(This is another incidental issue - I have to use a named range that calls the formula in the conditional formatting rule, instead of the formula itself.)

When I load the addin and open the workbook normally (e.g. double-click), the conditional formatting works fine. When I open the workbook using the ribbon (i.e. using Application.Workbooks.Open) the formatting doesn't work.

It's strange, since calling the function from a cell will still work and return TRUE, but conditional formatting doesn't.

What could be wrong here?

Thanks.

Nick

[Govert van Drimmelen]

Hi Nick,

I haven't tried this myself, but does it make a difference if you call

    EnableFormatConditionsCalculation=false

    EnableFormatConditionsCalculation=true

after opening the file?

-Govert

[Nick VG]

Thanks for the suggestion Govert. Unfortunately this doesn't work.

I've noticed one other thing though: if I simply make a copy of the worksheet, the new copy works fine.

I've tried comparing the properties of the original and copied worksheets manually, but can't see any differences. Seems to me to be an Excel bug.

Nick