CODEWITHSUNDEEP
c#excelexcel-interop
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10267

Why does FreezePane only work on the first sheet?

I have this code that works fine, adding a FreezePane to row 7, col 3 of the first sheet (counting from the left):

private void FreezePane(int rowNum, int colNum)
{
    Range cellToFreeze = (Range)_xlSheet.Cells[rowNum, colNum];
    cellToFreeze.Activate();
    cellToFreeze.Application.ActiveWindow.FreezePanes = true;
}

After adding another couple of sheets (the second sheet contains data as a source for a pivot table, and the third sheet contains the pivot table), I want to add a freezepane in the same place of the third/pivot Table sheet, and so I tried this:

private void FreezePanePivotTable(int rowToFreeze, int colToFreeze)
{
    Range pivotTableCellToFreeze = (Range)_xlPivotTableSheet.Cells[rowToFreeze, colToFreeze];
    pivotTableCellToFreeze.Activate();
    pivotTableCellToFreeze.Application.ActiveWindow.FreezePanes = true;
}

That, though, crashed with "Activate method of Range class failed Exception Source: Microsoft Office Excel Exception StackTrace: at System.RuntimeType.ForwardCallToInvokeMember(..."

So I thought, "maybe you can only have one frozen pane in a workbook" and tried calling only the new method (leaving the first sheet unfrozen), and I get this seemingly bizarre err msg: "Unable to set the Size property of the Font class"

Where this exception is occurring is in the second sheet - the source data for sheet 3/PivotTable sheet! Why does setting the font size all of a sudden cause a problem? Line 3418 is the last line below:

var itemCodeLabelCell = _xlPivotDataSheet.Cells[1, 1];
itemCodeLabelCell.Value2 = "ItemCode";
itemCodeLabelCell.Style.WrapText = false;
itemCodeLabelCell.Style.Font.Size = 12;

Do different rules apply to adding freezepanes on the main (first) sheet and others, or is it that FreezePanes and PivotTables can't coexist, or what?

Upvotes: 1

Views: 115

Answers (1)

Xodrow
Xodrow

Reputation: 315

SomeSheet.SomeRange.Activate() fails if SomeSheet is not already active, hence probably the "Activate method of Range class failed".

You must activate the sheet before:

_xlPivotTableSheet.Activate();
pivotTableCellToFreeze.Activate();

Upvotes: 1

Related Questions