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 Personalize the IDE.
Prerequisites
You need the following components to complete this walkthrough:
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.
Provide 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
On the Project menu, click Add New Item.
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.
From the Office Ribbon Controls tab of the Toolbox, drag a CheckBox control onto group1.
Click CheckBox1 to select it.
In the Properties window, change the following properties.
Property
Value
Name
Button
Label
Button
Add a second check box to group1, and then change the following properties.
Property
Value
Name
NamedRange
Label
NamedRange
Add a third check box to group1, and then change the following properties.
Property
Value
Name
ListObject
Label
ListObject
Add controls to the worksheet
Managed controls can only be added to host items, which act as containers. Because VSTO Add-in projects work with any open workbook, the VSTO 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
In the Ribbon Designer, double-click Button.
The Click event handler of the Button check box opens in the Code Editor.
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)
Dim worksheet As Microsoft.Office.Tools.Excel.Worksheet =
Globals.Factory.GetVstoObject(NativeWorksheet)
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
In Solution Explorer, select Ribbon1.cs or Ribbon1.vb.
On the View menu, click Designer.
In the Ribbon Designer, double-click NamedRange.
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 void NamedRange_Click(object sender, RibbonControlEventArgs e)
{
Worksheet worksheet = Globals.Factory.GetVstoObject(
Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1]);
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);
}
}
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)
Dim worksheet As Microsoft.Office.Tools.Excel.Worksheet =
Globals.Factory.GetVstoObject(NativeWorksheet)
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
In the Ribbon Designer, double-click ListObject.
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)
Dim worksheet As Microsoft.Office.Tools.Excel.Worksheet =
Globals.Factory.GetVstoObject(NativeWorksheet)
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
Add the following statements to the top of the Ribbon code file.
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 Persist dynamic controls in Office documents.
To remove controls from the worksheet
In Solution Explorer, select ThisAddIn.cs or ThisAddIn.vb.
On the View menu, click Code.
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)
If Globals.Factory.HasVstoObject(worksheet) And
Globals.Factory.GetVstoObject(worksheet).Controls.Count > 0 Then
Dim vstoWorksheet As Worksheet = Globals.Factory.GetVstoObject(worksheet)
While vstoWorksheet.Controls.Count > 0
Dim vstoControl As Object = vstoWorksheet.Controls(0)
vstoWorksheet.Controls.Remove(vstoControl)
End While
End If
End Sub
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.