Adding Controls at Runtime

So far, this chapter has described how to add controls to the document or worksheet at design time with the Visual Studio control toolbox. Often, the controls needed for your application need to be added (and deleted) at runtime. Consider again the worksheet shown in Figure 14.1 earlier in this chapter. Suppose that you want to provide a Trade button at the end of every row that shows a stock. This would be impossible to achieve by adding buttons at design time, because the number of stock rows will vary at runtime as the workbook is edited. You would need to add a button to the end of the row dynamically as stock is added at runtime.

VSTO provides a mechanism to add controls at runtime via the Controls collection, present on Word's Document class and Excel's Worksheet classes. This Controls collection works a bit differently from the Controls collection in Windows Forms. In the Controls collection associated with a Windows Forms form class, you can add controls at runtime by creating an instance of the control and adding it to the form's collection of controls. Then you can set positioning on the control you created:

Dim btn As New System.Windows.Forms.Button form1.Controls.Add(btn) btn.Left = 100 

The VSTO Controls collection cannot take this approach, because although the instance of the button could be added to the collection, there would be no way for the developer to change any positional properties on it; the positional properties are not available until the ActiveX control is created and connected to the Windows Forms control. There needs to be a way to return to the developer a wrapped control that has both the original control and the OLEObject or OLEControl. The VSTO Controls collection provides two mechanisms for adding controls:

• VSTO provides a generic AddControl method that can be used with any Windows Forms control. This method takes an instance of the Windows Forms control you want to add and returns to you the Microsoft.Office.Tools.Excel.OLEObject or Microsoft.Office.Tools.Word.OLEControl that contains the control you passed in. So the equivalent of the Windows Forms code above in VSTO is shown here. The main difference is that now you have to track two objects, the Button object and the OLEObject object, and remember to set positioning only on the OLEObject:

  Dim btn As New System.Windows.Forms.Button() Dim oleObject as Microsoft.Office.Tools.Excel.OLEObject oleObject = Me.Controls.Add(btn, 100, 100, 150, 100, "button1") oleObject.Left = 100 

• For common Windows Forms controls, a set of helper methods on the Controls collection will return the VSTO extended control with positioning information merged in. A method called AddButton is provided on Excel's Controls collection, for example. This method returns a Microsoft.Office.Tools.Excel.Controls.Button. The code below does the same thing as the code shown earlier, except that it frees you from having to track two objects:

  Dim btn As Microsoft.Office.Tools.Excel.Controls.Button btn = Me.Controls.AddButton(100, 100, 150, 100, "button1") btn.Left = 100 

Listing 14.5 shows code that dynamically adds a group box to an Excel worksheet using the AddControl mechanism. It doesn't even use the returned OLEObject, because it sets the position as part of the initial call to AddControl. Then it goes further and adds more RadioButton controls to that group box.

Listing 14.5. A VSTO Excel Customization That Adds a Group Box to an Excel Worksheet at Runtime

Public Class Sheet1   Public myGroupBox As GroupBox   Private Sub Sheet1_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     myGroupBox = New System.Windows.Forms.GroupBox()     ' Add the group box to the controls collection on the sheet     Me.Controls.AddControl( _       myGroupBox, 100, 100, 150, 100, "groupbox")     ' Set the title of the group box     myGroupBox.Text = "Insurance type"     ' Add the radio buttons to the groupbox     myGroupBox.Controls.Add(New RadioButton())     myGroupBox.Controls.Add(New RadioButton())     ' Set the text of the radio buttons     myGroupBox.Controls(0).Text = "Life"     myGroupBox.Controls(1).Text = "Term"     ' Arrange the radio buttons in the group box     myGroupBox.Controls(0).Top = myGroupBox.Top + 25     myGroupBox.Controls(1).Top = _       myGroupBox.Controls(0).Bottom + 20     ' iterate through each button in the controls collection     Dim rb As RadioButton     For Each rb In myGroupBox.Controls       rb.Left = myGroupBox.Left + 10     Next   End Sub End Class 

Working with the Controls Collection

The Controls collection provides a simple mechanism to add controls to your document or worksheet at runtime. Before we get into the details of the Controls collection, it is important to note that the implementation and methods exposed are different between Word and Excel. Although the behavior of the collection is the same in each application, it was necessary to have a different implementation to ensure that the collection takes advantage of the host application. If you want to add a control to Excel, for example, passing in an Excel.Range object for its position makes a lot of sense. If you want to add a control to Word, passing in a Word.Range object makes sense.

To illustrate using the collection, we start by looking at the helper methods available for all the supported Windows Forms controls that ship with the .NET Framework. The helper methods follow a common design pattern; call the method with positional arguments and an identifier, and the method returns you the wrapped type for the control.

Word has two overloads for each helper method:

• A method that takes a Word Range object, a width and height for the control in points, and a string name for the control that uniquely identifies it within the controls collection:

  Controls.AddButton(ActiveWindow.Selection.Range, _   100, 50, "NewButton") 

• A method that takes a left, top, width, and height for the control in points and a string name for the control that uniquely identifies it within the controls collection:

  Controls.AddMonthCalendar(10, 50, 100, 100, "NewCalendar") 

  Excel also has two overloads for each helper method:

• A method that takes an Excel range object and a string name for the control that uniquely identifies it within the controls collection. The control will be sized always to match the size of the range passed to the method:

  Controls.AddButton(Range("A1"), "NewButton") 

• A method that takes a left, top, width, and height for the controls in points and a string name for the control that uniquely identifies it within the controls collection:

  Controls.AddMonthCalendar(10, 50, 100, 100, "NewCalendar") 

After the control has been added to the document or worksheet, you can program against it just as you do a control added at design time. Table 14.3 shows the complete list of helper methods to add controls on the Controls collection.

Table 14.3. Add Methods on the Excel Controls Collection

Method Name	Return Type
AddButton	Microsoft.Office.Tools.Excel.Controls.Button
AddChart	Microsoft.Office.Tools.Excel.Chart
AddCheckBox	Microsoft.Office.Tools.Excel.Controls.CheckBox
AddCheckedListBox	Microsoft.Office.Tools.Excel.Controls.CheckedListBox
AddComboBox	Microsoft.Office.Tools.Excel.Controls.ComboBox
AddDataGridView	Microsoft.Office.Tools.Excel.Controls.DataGridView
AddDateTimePicker	Microsoft.Office.Tools.Excel.Controls.DateTimePicker
AddDomainUpDown	Microsoft.Office.Tools.Excel.Controls.DomainUpDown
AddHScrollBar	Microsoft.Office.Tools.Excel.Controls.HScrollBar
AddLabel	Microsoft.Office.Tools.Excel.Controls.Label
AddLinkLabel	Microsoft.Office.Tools.Excel.Controls.LinkLabel
AddListBox	Microsoft.Office.Tools.Excel.Controls.ListBox
AddListView	Microsoft.Office.Tools.Excel.Controls.ListView
AddMonthCalendar	Microsoft.Office.Tools.Excel.Controls.MonthCalendar
AddNumericUpDown	Microsoft.Office.Tools.Excel.Controls.NumericUpDown
AddPictureBox	Microsoft.Office.Tools.Excel.Controls.PictureBox
AddProgressBar	Microsoft.Office.Tools.Excel.Controls.ProgressBar
AddPropertyGrid	Microsoft.Office.Tools.Excel.Controls.PropertyGrid
AddRadioButton	Microsoft.Office.Tools.Excel.Controls.RadioButton
AddRichTextBox	Microsoft.Office.Tools.Excel.Controls.RichTextBox
AddTextBox	Microsoft.Office.Tools.Excel.Controls.TextBox
AddTrackBar	Microsoft.Office.Tools.Excel.Controls.TrackBar
AddTreeView	Microsoft.Office.Tools.Excel.Controls.TreeView
AddVScrollBar	Microsoft.Office.Tools.Excel.Controls.VScrollBar
AddWebBrowser	Microsoft.Office.Tools.Excel.Controls.WebBrowser

AddControl

Unfortunately, helper methods are not available for every control on your machine, so there needs to be a way to add controls outside the list in Table 14.3. To do this, the Controls collection provides an AddControl method that enables you to pass in an instance of any Windows Forms control, and it will return the OLEObject (for Excel) or the OLEControl (for Word) that can be used to position the control after it is added:

' Declare a OLEObject variable Dim myobj As Microsoft.Office.Interop.Excel.OLEObject ' Add the control to the A10 cell myobj = Controls.AddControl(New UserControl1(), _   Me.Range("A10"), "DynamicUserControl") // Reposition it to the top of B15 myobj.Top = Me.Range("B15").Top 

A common pitfall of using AddControl is forgetting to set the positioning on the OLEObject and setting it directly on the Windows Forms control itself. If you do this, the control will change its position relative to the container rather than move its position correctly in the document. For an example of this issue, consider Listing 14.3 and Figure 14.13.

Deleting Controls at Runtime

Now that we have some controls added to the document at runtime, it is important that there be a mechanism to delete controls from the collection. VSTO provides three ways to achieve this:

• Calling the Remove method on the Controls collection and passing in the instance or name of the control that you want to remove from the collection

• Calling the RemoveAt method on the Controls collection and passing in the index of the control to be removed

• Calling the Delete method on the control itself, which will in turn delete the control

You can delete only controls that have been added at runtime. If you try to remove controls that were added at design time, you will get an exception.

Controls Added at Runtime Not Saved

We wanted to keep the behavior of the Controls collection as close to the Windows Forms development experience so that any control added at runtime is deleted from the document when the user saves the document. If you add controls to a Windows Forms application at runtime, for example, you do not expect those controls just to appear the next time you run the application without code being written to re-create those controls. We spent many hours debating the relative merits of this approach versus the alternative, which was to allow Word or Excel to save the newly added control when the document was saved. The main deciding argument for not saving the newly added control was to make it easier to write dynamic control code in the document. If we had left the control in the document when the user saved the document, it would have been very difficult to write code that could connect controls that had been added dynamically the last time the document was open. To understand why this was difficult really involves looking into how a control is added to the document at runtime.

When a control is added to the Controls collection, the VSTO runtime adds an instance of the ActiveX control that will host the control and then sets it to host the provided control. This works fine when the document is running but quickly becomes complicated when the user saves the document. If we were to save the control into the document, all that would be stored would be the ActiveX control itself, but without any instance of the Windows Forms control, because it must be provided by the code at runtime. The next time the document loaded up, the ActiveX control would load but would not get an instance of the control, because the code that added the instance of the Windows Forms control would run again and add a new instance of the ActiveX control, because it would have no link back to the saved ActiveX control. Extrapolate this situation out over a few hundred saves of a document, and you quickly get a lot of "orphaned" ActiveX controls that will never be used.

The solution that was implemented in VSTO was to remove all ActiveX control instances that were added as a result of adding a control at runtime to the Controls collection. This way, there will never be any "orphaned" ActiveX controls on the document, and it also makes your code simpler to write. Why is the code simpler to write? Imagine writing the code to add the buttons at the end of each row containing a stock:

For Each stock As StockRow In Stocks   ' add stock information to row here   Me.Controls.AddButton( _     Me.Range(currentrow, "12"), stock.Ticker + "btn") Next 

If the control were persisted with the worksheet on save, the code would have to go through each control and ensure that the buttons added in the last run were thereand quite possibly delete and add them again, because the stock list had changed. We believed it was more straightforward just to iterate through the stocks on every run of the workbook and add the buttons.

Controls in the Controls Collection Typed as Object

VSTO documents and worksheets can have Windows Forms controls added to them at runtime via the Controls collection, as well as host controls such as NamedRange and ListObject. Both these types of controls act like controls in the VSTO model. You can click a NamedRange in VSTO and display a Visual Studio property window for it, for example. You can establish data bindings to a NamedRange just as you can with a text box or any other Windows Forms control.

As a result, the VSTO model considers both NamedRange and a Windows Forms control to be a "control" associated with the worksheet or document. The Controls collection contains both host controls and Windows Forms controls. Although providing a strongly typed collection was something that we would have liked to do, there was no common type other than Object that a host control and a Windows Forms control share.