CODEWITHSUNDEEP
c#excelcontrolsscrollbaroffice-interop
Jan021981
Jan021981

Reputation: 553

Accessing Scrollbar control on excel sheet using C#

I want to add an Scrollbar control to my excel sheet using . I don't want to use the Windows.Forms.Scrollbar but the one, Excel provides.

Adding the control is no problem - my problem is to access the created control to define the minimum, maximum and the cell-reference. Here is my code:

    public static void AddScrollBar(Ex.Worksheet ExSH, int StartCellRow, int StartCellColumn, int EndCellRow, int EndCellColumn, int ReferenceRow, int ReferenceColumn, int MaxValue, int MinValue = 0, int CurrentValue = 0)
    {
        int Left, Top, Right, Bottom;
        if(StartCellRow > EndCellRow || StartCellColumn > EndCellColumn) { throw new System.Exception("EROR: Please check Start- and Endcell reference or flip it!"); }

        Left = (int)(ExSH.Cells[StartCellRow, StartCellColumn] as Ex.Range).Left;
        Top = (int)(ExSH.Cells[StartCellRow, StartCellColumn] as Ex.Range).Top;
        Right = (int)((ExSH.Cells[EndCellRow, EndCellColumn] as Ex.Range).Left + (ExSH.Cells[EndCellRow, EndCellColumn] as Ex.Range).Width);
        Bottom = (int)((ExSH.Cells[EndCellRow, EndCellColumn] as Ex.Range).Top + (ExSH.Cells[EndCellRow, EndCellColumn] as Ex.Range).Height);

        Ex.ControlFormat SB = ExSH.Shapes.AddFormControl(Ex.XlFormControl.xlScrollBar, Left, Top, Right - Left, Bottom - Top) as Ex.ControlFormat;
        SB.Value = CurrentValue; /*ERROR: Object reference not set to an instance of an object*/
        SB.Min = MinValue;
        SB.Max = MaxValue;
        SB.LinkedCell = (ExSH.Cells[ReferenceRow, ReferenceColumn] as Ex.Range).ToString();
    }

The error occurs in Line 12 (SB.Value = ...)
When I comment the lines SB.Value = ... to SB.LindedCell = ..., the code runs well. Opening the created Excel Workbook and selecting the depending Worksheet, I can find the created scrollbar where it should be. When I break after the creation of the scrollbar, SB is .

Can anybody help me? I'm not so skilled in advances stuff.

Thank you in advance, Jan

Upvotes: 0

Views: 410

Answers (1)

Jan021981
Jan021981

Reputation: 553

Ah, I found it. SB is a Ex.Shape. And then, you can access the parameter by SB.ControlFormat.Min, Max, Value etc.

Here is the working code:

    public static void AddScrollBar(Ex.Worksheet ExSH, int StartCellRow, int StartCellColumn, int EndCellRow, int EndCellColumn, int ReferenceRow, int ReferenceColumn)
    {
        int Left, Top, Right, Bottom;
        if(StartCellRow > EndCellRow || StartCellColumn > EndCellColumn) { throw new System.Exception("EROR: Please check Start- and Endcell reference or flip it!"); }

        Left = (int)(ExSH.Cells[StartCellRow, StartCellColumn] as Ex.Range).Left;
        Top = (int)(ExSH.Cells[StartCellRow, StartCellColumn] as Ex.Range).Top;
        Right = (int)((ExSH.Cells[EndCellRow, EndCellColumn] as Ex.Range).Left + (ExSH.Cells[EndCellRow, EndCellColumn] as Ex.Range).Width);
        Bottom = (int)((ExSH.Cells[EndCellRow, EndCellColumn] as Ex.Range).Top + (ExSH.Cells[EndCellRow, EndCellColumn] as Ex.Range).Height);

        Ex.Shape SB = ExSH.Shapes.AddFormControl(Ex.XlFormControl.xlScrollBar, Left, Top, Right - Left, Bottom - Top);
        SB.ControlFormat.Value = CurrentValue;
        SB.ControlFormat.Min = MinValue;
        SB.ControlFormat.Max = MaxValue;
        SB.ControlFormat.LinkedCell = "A" + ReferenceRow.ToString();
    }

Upvotes: 2

Related Questions