B. Clay Shannon B. Clay Shannon - 1 month ago 7
C# Question

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?

Answer

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();
Comments