Walkthrough: Adding Controls to a Worksheet at Run Time in an Application-Level Project

  • Article

You can add controls to any open worksheet by using an Excel add-in. This walkthrough demonstrates how to use the Ribbon to enable users to add a Button, a NamedRange, and a ListObject to a worksheet. For information, see Adding Controls to Office Documents at Run Time.

Applies to: The information in this topic applies to application-level projects for Excel 2007 and Excel 2010. For more information, see Features Available by Office Application and Project Type.

This walkthrough illustrates the following tasks:

  • Providing a user interface (UI) to add controls to the worksheet.

  • Adding controls to the worksheet.

  • Removing controls from the worksheet.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

Prerequisites

You need the following components to complete this walkthrough:

-

An edition of Visual Studio 2010 that includes the Microsoft Office developer tools. For more information, see [Configuring a Computer to Develop Office Solutions](bb398242\(v=vs.100\).md).
  • Microsoft Office Excel 2007 or Excel 2010.

Creating a New Excel Add-in Project

Start by creating an Excel add-in project.

To create a new Excel add-in project

  1. In Visual Studio, create an Excel add-in project with the name ExcelDynamicControls. For more information, see How to: Create Office Projects in Visual Studio.

  2. If your project targets the .NET Framework 4, add a reference to the Microsoft.Office.Tools.Excel.v4.0.Utilities.dll assembly. This reference is required to programmatically add a Windows Forms control to a worksheet later in this walkthrough.

Providing a UI to Add Controls to a Worksheet

Add a custom tab to the Excel Ribbon. Users can select check boxes on the tab to add controls to a worksheet.

To provide a UI to add controls to a worksheet

  1. On the Project menu, click Add New Item.

  2. In the Add New Item dialog box, select Ribbon (Visual Designer), and then click Add.

    A file named Ribbon1.cs or Ribbon1.vb opens in the Ribbon Designer and displays a default tab and group.

  3. From the Office Ribbon Controls tab of the Toolbox, drag a CheckBox control onto group1.

  4. Click CheckBox1 to select it.

  5. In the Properties window, change the following properties.

    Property

    Value

    Name

    Button

    Label

    Button

  6. Add a second check box to group1, and then change the following properties.

    Property

    Value

    Name

    NamedRange

    Label

    NamedRange

  7. Add a third check box to group1, and then change the following properties.

    Property

    Value

    Name

    ListObject

    Label

    ListObject

Adding Controls to the Worksheet

Managed controls can only be added to host items, which act as containers. Because add-in projects work with any open workbook, the add-in converts the worksheet into a host item, or gets an existing host item, before adding the control. Add code to the click event handlers of each control to generate a Worksheet host item that is based on the open worksheet. Then, add a Button, a NamedRange, and a ListObject at the current selection in the worksheet.

To add controls to a worksheet

  1. In the Ribbon Designer, double-click Button.

    The Click event handler of the Button check box opens in the Code Editor.

  2. Replace the Button_Click event handler with the following code.

    This code uses the GetVstoObject method to get a host item that represents the first worksheet in the workbook, and then adds a Button control to the currently selected cell.

    Private Sub Button_Click(ByVal sender As System.Object, _
    ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) _
        Handles Button.Click

    Dim NativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet =
        Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(1)

    ' Use the following line of code in projects that target the .NET Framework 4.
    Dim worksheet As Microsoft.Office.Tools.Excel.Worksheet =
        Globals.Factory.GetVstoObject(NativeWorksheet)

    ' In projects that target the .NET Framework 3.5, use the following line of code.
    ' Dim worksheet = CType(Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(1),  _
    '    Excel.Worksheet).GetVstoObject()

    Dim buttonName As String = "MyButton"

    If CType(sender, RibbonCheckBox).Checked Then
        Dim selection As Excel.Range = Globals.ThisAddIn.Application.Selection
        If Not (selection Is Nothing) Then
            Dim button As New Microsoft.Office.Tools.Excel.Controls.Button()
            worksheet.Controls.AddControl(button, selection, buttonName)
        End If
    Else
        worksheet.Controls.Remove(buttonName)
    End If
End Sub

    private void Button_Click(object sender, RibbonControlEventArgs e)
{
    // Use the following line of code in projects that target the .NET Framework 4.
    Worksheet worksheet = Globals.Factory.GetVstoObject(
        Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1]);

    // In projects that target the .NET Framework 3.5, use the following line of code.
    // Worksheet worksheet = 
    //     ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1]).GetVstoObject();

    string buttonName = "MyButton";

    if (((RibbonCheckBox)sender).Checked)
    {
        Excel.Range selection = Globals.ThisAddIn.Application.Selection as Excel.Range;
        if (selection != null)
        {
            Microsoft.Office.Tools.Excel.Controls.Button button =
                new Microsoft.Office.Tools.Excel.Controls.Button();
            worksheet.Controls.AddControl(button, selection, buttonName);
        }
    }
    else
    {
        worksheet.Controls.Remove(buttonName);
    }
}

  3. In Solution Explorer, select Ribbon1.cs or Ribbon1.vb.

  4. On the View menu, click Designer.

  5. In the Ribbon Designer, double-click NamedRange.

  6. Replace the NamedRange_Click event handler with the following code.

    This code uses the GetVstoObject method to get a host item that represents the first worksheet in the workbook, and then defines a NamedRange control for the currently selected cell or cells.

    Private Sub NamedRange_Click(ByVal sender As System.Object, _
    ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) _
        Handles NamedRange.Click

    Dim NativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet =
        Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(1)

    ' Use the following line of code in projects that target the .NET Framework 4.
    Dim worksheet As Microsoft.Office.Tools.Excel.Worksheet =
        Globals.Factory.GetVstoObject(NativeWorksheet)

    ' In projects that target the .NET Framework 3.5, use the following line of code.
    ' Dim worksheet = CType(Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(1),  _
    '    Excel.Worksheet).GetVstoObject()

    Dim rangeName As String = "MyNamedRange"

    If CType(sender, RibbonCheckBox).Checked Then
        Dim selection As Excel.Range = Globals.ThisAddIn.Application.Selection
        If Not (selection Is Nothing) Then
            Dim namedRange As NamedRange = _
                worksheet.Controls.AddNamedRange(selection, rangeName)
        End If
    Else
        worksheet.Controls.Remove(rangeName)
    End If
End Sub

    private void NamedRange_Click(object sender, RibbonControlEventArgs e)
{
    // Use the following line of code in projects that target the .NET Framework 4.
    Worksheet worksheet = Globals.Factory.GetVstoObject(
        Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1]);

    // In projects that target the .NET Framework 3.5, use the following line of code.
    // Worksheet worksheet = 
    //     ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1]).GetVstoObject();

    string Name = "MyNamedRange";

    if (((RibbonCheckBox)sender).Checked)
    {
        Excel.Range selection = Globals.ThisAddIn.Application.Selection as Excel.Range;
        if (selection != null)
        {
            worksheet.Controls.AddNamedRange(selection, Name);
        }
    }
    else
    {
        worksheet.Controls.Remove(Name);
    }
}

  7. In the Ribbon Designer, double-click ListObject.

  8. Replace the ListObject_Click event handler with the following code.

    This code uses the GetVstoObject method to get a host item that represents the first worksheet in the workbook, and then defines a ListObject for the currently selected cell or cells.

    Private Sub ListObject_Click(ByVal sender As System.Object, _
    ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) _
        Handles ListObject.Click

    Dim NativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet =
        Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(1)

    ' Use the following line of code in projects that target the .NET Framework 4.
    Dim worksheet As Microsoft.Office.Tools.Excel.Worksheet =
        Globals.Factory.GetVstoObject(NativeWorksheet)

    ' In projects that target the .NET Framework 3.5, use the following line of code.
    ' Dim worksheet = CType(Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(1),  _
    '    Excel.Worksheet).GetVstoObject()

    Dim listObjectName As String = "MyListObject"

    If CType(sender, RibbonCheckBox).Checked Then
        Dim selection As Excel.Range = _
            Globals.ThisAddIn.Application.Selection
        If Not (selection Is Nothing) Then
            worksheet.Controls.AddListObject(selection, listObjectName)
        End If
    Else
        worksheet.Controls.Remove(listObjectName)
    End If
End Sub

    private void ListObject_Click(object sender, RibbonControlEventArgs e)
{
    // Use the following line of code in projects that target the .NET Framework 4.
    Worksheet worksheet = Globals.Factory.GetVstoObject(
        Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1]);

    // In projects that target the .NET Framework 3.5, use the following line of code.
    // Worksheet worksheet = 
    //     ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1]).GetVstoObject();

    string listObjectName = "MyListObject";

    if (((RibbonCheckBox)sender).Checked)
    {
        Excel.Range selection = Globals.ThisAddIn.Application.Selection as Excel.Range;
        if (selection != null)
        {
            worksheet.Controls.AddListObject(selection, listObjectName);
        }
    }
    else
    {
        worksheet.Controls.Remove(listObjectName);
    }
}

  9. Add the following statements to the top of the Ribbon code file.

    Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Tools.Excel
Imports Microsoft.Office.Tools.Excel.Extensions

    using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Tools.Excel;
using Microsoft.Office.Tools.Excel.Extensions;

Removing Controls from the Worksheet

Controls are not persisted when the worksheet is saved and closed. You should programmatically remove all generated Windows Forms controls before the worksheet is saved, or only an outline of the control will appear when the workbook is opened again. Add code to the WorkbookBeforeSave event that removes Windows Forms controls from the controls collection of the generated host item. For more information, see Persisting Dynamic Controls in Office Documents.

To remove controls from the worksheet

  1. In Solution Explorer, select ThisAddIn.cs or ThisAddIn.vb.

  2. On the View menu, click Code.

  3. Add the following method to the ThisAddIn class. This code gets the first worksheet in the workbook and then uses the HasVstoObject method to check whether the worksheet has a generated worksheet object. If the generated worksheet object has controls, the code gets that worksheet object and iterates through the control collection, removing the controls.

    Sub Application_WorkbookBeforeSave _
    (ByVal workbook As Microsoft.Office.Interop.Excel.Workbook, _
     ByVal SaveAsUI As Boolean, ByRef Cancel As Boolean) _
     Handles Application.WorkbookBeforeSave

    Dim worksheet As Excel.Worksheet = workbook.Worksheets(1)

    ' Use the following code in projects that target the .NET Framework 4.
    If Globals.Factory.HasVstoObject(worksheet) And
        Globals.Factory.GetVstoObject(worksheet).Controls.Count > 0 Then
        Dim vstoWorksheet As Worksheet = Globals.Factory.GetVstoObject(worksheet)

        ' In projects that target the .NET Framework 3.5, use the following code.
        ' If worksheet.HasVstoObject() And worksheet.GetVstoObject().Controls.Count > 0 Then
        ' Dim vstoWorksheet As Worksheet = worksheet.GetVstoObject()

        While vstoWorksheet.Controls.Count > 0
            Dim vstoControl As Object = vstoWorksheet.Controls(0)
            vstoWorksheet.Controls.Remove(vstoControl)
        End While
    End If
End Sub

    void Application_WorkbookBeforeSave(Microsoft.Office.Interop.Excel.Workbook workbook, 
    bool SaveAsUI, ref bool Cancel)
{
    Excel.Worksheet worksheet =
        workbook.Worksheets[1] as Excel.Worksheet;

    // Use the following lines of code in projects that target the .NET Framework 4.
    if (Globals.Factory.HasVstoObject(worksheet) && 
        Globals.Factory.GetVstoObject(worksheet).Controls.Count > 0)
    {
        Worksheet vstoWorksheet = Globals.Factory.GetVstoObject(worksheet);

    // In projects that target the .NET Framework 3.5, use the following line of code.
    // if (worksheet.HasVstoObject() && worksheet.GetVstoObject().Controls.Count > 0)
    // {
    //     Worksheet vstoWorksheet = worksheet.GetVstoObject();               

        while (vstoWorksheet.Controls.Count > 0)
        {
            object vstoControl = vstoWorksheet.Controls[0];
            vstoWorksheet.Controls.Remove(vstoControl);
        }

    }
}

  4. In C#, you must create an event handler for the WorkbookBeforeSave event. You can place this code in the ThisAddIn_Startup method. For more information about creating event handlers, see How to: Create Event Handlers in Office Projects. Replace the ThisAddIn_Startup method with the following code.

    private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
    this.Application.WorkbookBeforeSave += 
        new Microsoft.Office.Interop.Excel.AppEvents_WorkbookBeforeSaveEventHandler
            (Application_WorkbookBeforeSave);
}

Testing the Solution

Add controls to a worksheet by selecting them from a custom tab on the Ribbon. When you save the worksheet, these controls are removed.

To test the solution.

  1. Press F5 to run your project.

  2. Select any cell in Sheet1.

  3. Click the Add-Ins tab.

  4. In the group1 group, click Button.

    A button appears in the selected cell.

  5. Select a different cell in Sheet1.

  6. In the group1 group, click NamedRange.

    A named range is defined for the selected cell.

  7. Select a series of cells in Sheet1.

  8. In the group1 group, click ListObject.

    A list object is added for the selected cells.

  9. Save the worksheet.

    The controls that you added to Sheet1 no longer appear.

Next Steps

You can learn more about controls in Excel add-in projects from this topic:

See Also

Concepts

Using Windows Forms Controls on Excel Worksheets

NamedRange Control

ListObject Control

Other Resources

Excel Solutions

Controls on Office Documents

Change History

Date

History

Reason

April 2011

Added text that describes an assembly reference that you must add if your project targets the .NET Framework 4.

Content bug fix.