One late breaking feature in VSTO 2.0 is known as dynamic controls. This is a feature that allows you to add managed controls (think buttons, labels, calendar controls) and view controls (think controls that are ‘built-in’ to the host like Range and ListObject for Excel and Bookmark for Word) at runtime.
If you go into any view class of the Excel project in VSTO (Sheet1, Sheet2, Sheet3) or any view class of the Word project in VSTO (Document) you will find a “Controls” collection object is provided as a member of each view class that lets you get at “static” controls as well as dynamic controls. Static controls are the ones you added at design-time by dragging and dropping controls onto the document from the Visual Studio toolbox. Dynamic controls are ones you added via code at run-time.
You can enumerate over all the controls, find by index, name, etc. When you add a dynamic control, you must provide a name that is a unique identifier for the given worksheet or document. You get at “static” controls by passing the identifier associated with that control, e.g. “Button1”. Note that you cannot remove “static” controls at runtime.
You can now write this code in a VB sheet to add and mess around with buttons dynamically. This adds buttons to an Excel worksheet in a little matrix pattern:
Dim i As Integer
Dim j As Integer
For i = 0 To 2
For j = 0 To 2
Dim myButton As Microsoft.Office.Tools.Excel.Controls.Button = Me.Controls.AddButton(i * 100, j * 100, 50, 50, “Button” + Trim(Str$(i)) + Trim(Str$(j)))
myButton.Text = “Button ” + Str$(i) + Str$(j)
The code above specifies an X, Y coordinate in the coordinate space of Excel. You can also add a button in Excel and pass a range. This causes the button to be locked to that range and it will resize when the range is resized:
‘ A button added with a range argument will resize with the range
Dim myButton2 As Microsoft.Office.Tools.Excel.Controls.Button = Me.Controls.AddButton(Me.Range(1, 1), 50, 50, “Range Button”)
Here’s some code showing how you would iterate over the controls collection:
Dim o As Object
For Each o In Me.Controls
If TypeOf o Is Microsoft.Office.Tools.Excel.Controls.Button Then
Dim b As Microsoft.Office.Tools.Excel.Controls.Button
b = CType(o, Microsoft.Office.Tools.Excel.Controls.Button)
b.BackColor = System.Drawing.Color.FromArgb(Rnd(1) * 255, Rnd(1) * 255, Rnd(1) * 255)
This code shows removing dynamically added controls at runtime:
‘ Dynamic controls can be removed at runtime
Finally, code that adds a ListObject in Excel dynamically:
Me.Controls.AddListObject(Me.Range(Me.Cells(1, 1), Me.Cells(4, 4)), “List1”)
This should give you a quick idea of how you add controls at runtime. I will try to blog about more of the details in future entries.