Export (0) Print
Expand All
Expand Minimize

Excel 2003 and Visual Studio 2005 Tools for Office Solution: Using Dynamic Controls and Worksheets

Office 2003

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Chris Kunicki, www.OfficeZealot.com

Published: January 2005

Updated: January 2006

Applies to: Microsoft Visual Studio 2005 Tools for the Microsoft Office System, Microsoft Office Excel 2003

Summary: Learn how to use dynamic Microsoft .NET Framework managed controls and dynamic worksheets in an Excel 2003 solution using Microsoft Visual Studio 2005 Tools for Office. The associated code sample is provided in Visual Basic and C#. (16 printed pages)

Download OfficeVSTODynamicControls.msi.

Contents

Microsoft Office Excel provides flexibility in crunching numbers and analyzing data. Even so, you may want to provide custom functionality or an alternative way of interacting with an Excel workbook. While providing custom functionality has been easy to do for years with Microsoft Visual Basic for Applications (VBA) or Microsoft Visual Studio Tools for Office, Version 2003, it has not been easy to provide alternative ways of interacting with the Excel user interface, until recently.

Visual Studio Tools for Office, Version 2003 gave developers the ability to write Microsoft .NET code behind Microsoft Office Word and Excel documents. Visual Studio 2005 Tools for Office goes much further. With Visual Studio 2005 Tools for Office, you can now create managed controls on the document surface, create custom task panes, data bind document elements to an external data source, programmatically change documents on a server, and much more.

This article focuses on the new ability of Visual Studio 2005 Tools for Office to create managed controls in Excel dynamically. It introduces a sample solution and discusses why managed controls benefit Microsoft Office solutions.

Why Managed Controls?

Using controls in an Excel workbook is not a new idea. For years, developers used Microsoft ActiveX controls, such as the TreeView control, DatePicker control, Web Browser control, and any of the built-in Excel form controls on a worksheet. These controls provided developers with a different way to display information or with a way to give users an alternative method to navigate information.

Developers used this functionality, but often with extreme caution. Many developers found combining Excel and ActiveX controls difficult for these reasons:

  • ActiveX controls require a client-side installation and registration step. Because many developers do not control the desktop of their users, this can be a challenge.

  • ActiveX controls in Excel can behave unpredictably and require intensive testing to assure reliability. Excel and other Microsoft Office products are not ideal ActiveX containers.

  • Some developers consider ActiveX controls to be a possible security risk.

It was often easier to settle with a less useful solution to avoid possible problems with ActiveX. This did not mean that developers did not want to use controls, but that doing so was rarely worth the hassle.

The Microsoft .NET Framework addresses these concerns. Microsoft .NET includes many useful controls as part of the .NET Framework, removing the need to deploy controls. Also, the .NET Framework provides several ways to distribute more securely your own custom controls or those bought from third parties. Visual Studio 2005 Tools for Office resolves the stability of Excel and controls. Visual Studio 2005 Tools for Office makes working with managed controls in Excel reliable during design-time and at run time.

From personal experience, I can tell you that seeing managed controls run reliably and securely in Excel is exciting. Using the ActionsPane object to combine managed controls with the Visual Studio 2005 Tools for Office task pane programmability opens a new world for Excel solution development that goes beyond simply providing custom menus and toolbars in your solution.

Business Scenario

To help you better understand the value and use of managed controls in an Excel solution, I developed this article around a common, Excel-based business scenario. The business scenario revolves around the Corporate Services Business unit of the fictional Woodgrove Bank.

The Corporate Services Business unit provides customized banking services and products to large corporate customers who want to outsource common employee-related retirement and investment benefits. For example, a company might hire Woodgrove Bank to provide their employees with a customized 401(k) retirement savings plan offering and training. One challenge with customized banking services is the extended sales closing process. Management requires their salespeople to closely track potential sales leads and current status. Each month, management consolidates and rolls-up all sales leads for analysis.

To aid the individual salespeople in this process, management created an Excel workbook called Sales Lead Tracking. Figure 1 shows a worksheet in the Sales Lead Tracking workbook with information about the Litware Inc. sales lead.

Figure 1. Sales Lead Tracking workbook

Sales Lead Tracking workbook

Sales lead tracking is a common requirement of many organizations that sell products and services with long closing cycles. The Sales Lead Tracking workbook helps organize the following information:

  • Individual sales opportunities

  • Information about the customer (name, address, phone number, e-mail address)

  • The primary contact

  • The products or services being pitched

  • The status of the sales opportunity

  • The dates of the last customer contact and the next contact date

Management reviews this information on a monthly and quarterly basis to analyze:

  • Individual and sales team performance and effectiveness

  • Future revenue and resource utilization requirements

In this workbook, each salesperson tracks each individual sales opportunity that he or she pursues. For each individual opportunity, the salesperson creates a separate worksheet and collects common information such as customer information, products and services, and the status of the opportunity. The first worksheet in the Sales Lead Tracking workbook is a Summary worksheet that consolidates all the opportunities into one master view, as shown in Figure 2.

Figure 2. Summary worksheet with consolidated leads

Summary worksheet with consolidated leads

Now that you understand the business scenario, you can work with controls in code.

In many ways, working with Excel and managed controls in Visual Studio 2005 Tools for Office is similar to working with controls in Microsoft Windows Forms development. In Windows Forms development, you simply drag controls from the toolbox onto the Windows Form. You then set the properties of controls in the Properties window. You can programmatically alter a Windows Form and its controls from code. As stated earlier, the experience is very similar when using Excel with Visual Studio 2005 Tools for Office.

Figure 3 shows Visual Studio 2005 with an Excel workbook open in the designer. Visual Studio 2005 Tools for Office loads Excel as a child window into Visual Studio to give you a first-class design experience. In the designer, you can drag controls from the toolbox onto the worksheet. The Properties window enables you to change the properties of the selected control. As you drag controls onto the worksheet and change their properties, Visual Studio 2005 Tools for Office generates the underlying code to recreate these controls at run time. The following illustration shows a DatePicker control being dragged from the toolbox to the Microsoft Excel design surface.

Figure 3. Visual Studio 2005 Tools for Office with Excel open in the designer

Visual Studio Tools for Office with Excel open

New Visual Studio 2005 Tools for Office Objects

You can access all the controls created during development programmatically, and you can dynamically create controls on the fly. The Visual Studio 2005 Tools for Office runtime has a few new objects with which you interact when working with controls.

  • Host item. In Visual Studio 2005 Tools for Office, the Word Document and Excel Worksheet classes are known as host items. Host items provide a view on data and are a container for controls, such as host controls and Windows Forms controls. There are two more host items in Excel, the Workbook and ChartSheet, but these do not act as containers for host controls. You should treat a host item as a helpful wrapper around built-in Excel objects, such as the workbook and worksheet.

  • Host control. A host item and a host control differ from each other. Host controls extend existing Word and Excel objects. Host controls added to Office documents behave like native Office objects, such as ranges; however, they have additional functionality, including events and data binding capabilities.

    For example, when you want to capture the events of a native Range object in Excel, you must trap the change event of the worksheet, and then determine if the change occurred in the range in which you are interested. With the NamedRange host control, you can access the change event directly. The Sales Lead Tracking solution uses the NamedRange host control to detect when the user changes the Opportunity Name on a sales lead worksheet.

    Do not confuse host controls with Windows Forms controls. Host controls are helper controls that enhance built-in Excel functionality.

  • Globals object. The Globals object is a helper object that, as its name implies, represents a global view of the document or workbook in your solution. The Globals object gives you easy access to the Workbook, all the Worksheets created at design time, and the Excel Application object. If you are familiar with Excel VBA programming, you might find it helpful to compare the Globals object to the ActiveWorkbook object.

    The Globals object has a Worksheet object for each sheet in your workbook at design time. For example, if your workbook contains three sheets named Sheet1, Sheet2, and Sheet3, the Globals object has three child objects. You access them as:

    Globals.Sheet1
    Globals.Sheet2
    Globals.Sheet3
    

    The Globals object is a helper object created during design time and is only aware of worksheets created in the Visual Studio designer. For this reason, if a user adds new worksheets to a workbook at run time, you cannot depend on Globals to access these new worksheets. Later you will see how to create a dynamic Worksheet object from worksheets added at run time.

Working with Controls

The following code shows how to reference each control in the Controls collection of Sheet1 in an Excel workbook. As the code reaches each control, it displays the type name of the control in a message box:

Dim myControl As Object
For Each myControl In Globals.Sheet1.Controls
    MessageBox.Show(myControl.GetType().FullName)
Next

Notice that the variable myControl is an object. Because the worksheet can contain many control types, the object type is generic. If you know the type of the control, you can easily cast the control to its correct type. As noted earlier, Sheet1 is a Worksheet object that gives you programmatic access to the Controls collection on that worksheet.

To dynamically create a control at run time, the following code snippet dynamically creates a DateTimePicker control, and then adds it to Sheet 1:

Dim myDatePicker As Microsoft.Office.Tools.Excel.Controls.DateTimePicker
myDatePicker = Globals.Sheet1.Controls.AddDateTimePicker(1, 1, _
    200, 200, "MyDatePicker")

First the code declares a DateTimePicker object. Second, it accesses the control container collection of the worksheet through Globals.Sheet1.Controls. The Controls collection represents all controls on the Worksheet, but also allows you to add new controls to the collection. The first four parameters of the AddDateTimePicker method deal with control positioning and size. The final parameter is a unique name for the new control on the worksheet.

After running this code, you see a new DateTimePicker control on the Sheet1 worksheet, aligned in the upper-right corner.

The Visual Studio 2005 Tools for Office runtime provides several helper methods in the Controls collection for adding common controls. In addition to the AddDateTimePicker method, the Controls collection includes the AddCheckBox, AddLabel, AddButton, and AddTextBox methods. There are about 28 helper functions for adding standard .NET Windows Forms controls to the workbook.

These helper functions also have a method override that accepts a range for positioning the control in the worksheet instead of using exact physical coordinates. The following code creates a DateTimePicker control and places it at range A1.

Dim myRange As Excel.Range = Globals.Sheet1.Range("A1")
Dim myDatePicker As Microsoft.Office.Tools.Excel.Controls.DateTimePicker
myDatePicker = Globals.Sheet1.Controls.AddDateTimePicker(myRange, _
    "MyDatePicker")

By default, the new DateTimePicker moves and resizes with the underlying range. You can change this behavior through the control's Placement property. Using a range for placing controls simplifies the process of getting a control exactly where you want it on a worksheet and enables the Visual Studio 2005 Tools for Office runtime to manage keeping the control correctly positioned when the underlying range resizes or moves.

If you want to add a Windows Forms control or custom control for which the Visual Studio 2005 Tools for Office runtime does not provide a helper function, you can use the AddControl method of the Controls collection. The following code snippet shows how to add a TrackerBar control to the worksheet:

Dim myTrackerBar As New Windows.Forms.TrackBar
Globals.Sheet1.Controls.AddControl(myTrackerBar, 500, 500, 200, 100, _
    "MyTrackerBar")
myTrackerBar.TickStyle = TickStyle.BottomRight

This code first creates a new instance of a managed control of the type Windows.Forms.TrackBar. Then, the code calls the Controls collection AddControl method. The AddControl method takes the TrackerBar control as its first parameter, along with the information needed to position the control on the workbook. Once the control is in place, you can change the TrackerBar properties programmatically.

Dynamic Worksheets

Up to this point, the code showed what has worked against one of the worksheets available to us from the Globals object. How do we add controls to a worksheet created at run time by the user, knowing that the Globals object is only aware of worksheets created in the Visual Studio designer? The Visual Studio 2005 Tools for Office runtime does not provide a helper function to easily create a Worksheet object. To do this, we must use the runtime internals. The following code creates a Worksheet object based on a specified worksheet:

Private Function GetExtendedWorksheet( _
    ByRef nativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet) _
    As Microsoft.Office.Tools.Excel.Worksheet
    ' Get the IHostItemProvider instance.
    Dim hostItemProvider As _
        Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider = _
        RuntimeCallback.GetService( _
        GetType( _
        Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider))
    ' Create the new worksheet and return it to calling function.
    Return New Microsoft.Office.Tools.Excel.Worksheet(hostItemProvider, _
        RuntimeCallback, _
        nativeWorksheet.CodeName, _
        Container, _
        nativeWorksheet.Name)
End Function

Although the parameter and return type are both Worksheet objects, they are from different libraries. GetExtendedWorksheet takes a Worksheet object, which is a native Excel worksheet, and returns a Visual Studio 2005 Tools for Office Worksheet object. Remember, this Worksheet object is a wrapper around the Excel worksheet that gives you extended functionality. To add controls at run time to a new worksheet, you must get a Worksheet object. Using this function solves that problem.

This section shows how you can put the techniques described earlier to use in the business scenario. The salesperson using the Sales Lead Tracking workbook might generate anywhere from 10 to 50 leads each month. To add a new lead to the workbook, the salesperson uses the Add a New Sales Lead feature on the Summary worksheet. The salesperson types a name in the text box, and then clicks Add Lead, as shown in Figure 4.

Figure 4. Adding a new sales lead

Adding a new sales lead

Clicking Add Lead calls the CreateNewLeadWorksheet function with the information collected in the Add a New Sales Lead text box. The CreateNewLeadWorksheet function takes the name of the lead and creates a new worksheet with the name of the lead, basing it on a template that is marked as xlSheetVeryHidden. xlSheetVeryHidden tells Excel to hide this worksheet from the user. (The only way to access it is through code.) This allows us to hide the worksheet until we are ready to copy it.

First, CreateNewLeadWorksheet uses standard Excel object model methods to copy the worksheet and set various cell values. In addition, CreateNewLeadWorksheet adds the new lead to the master list of leads on the Summary sheet, in the top row of the list. CreateNewLeadWorksheet calls the AddDynamicControlsToLeadWorksheet function. The unique work happens in the AddDynamicControlsToLeadWorksheet function, as shown in this example:

Private Sub AddDynamicControlsToLeadWorksheet(ByRef sheet As _
        Microsoft.Office.Interop.Excel.Worksheet)
    Dim dynamicWorksheetView As Microsoft.Office.Tools.Excel.Worksheet
    Static controlCounter As Integer = 0
    Try
        'To create controls dynamically we need to create a
        'VSTO 2005 worksheet host control
        dynamicWorksheetView = GetExtendedWorksheet(sheet)

        'CREATE Initial Date Range Date Picker
        Dim initialDateRange As Excel.Range = _
        dynamicWorksheetView.Range("InitialContact")
        Dim initialDateDatePicker As _
            Microsoft.Office.Tools.Excel.Controls.DateTimePicker = _
        dynamicWorksheetView.Controls.AddDateTimePicker _
            (initialDateRange, "InitialDate" & controlCounter)
        initialDateDatePicker.Width = 150
        If initialDateRange.Value Is Nothing Then
            'If no date is specified, default to today
            initialDateDatePicker.Value = Date.Today
            initialDateRange.Value = Date.Today
        Else
            'Use existing date if it exists
            initialDateDatePicker.Value = initialDateRange.Value
        End If
        AddHandler initialDateDatePicker.ValueChanged, _
        AddressOf initialDateTimePicker_ValueChanged

        'CREATE Followup Date Range Date Picker
        Dim followupDateRange As Excel.Range = sheet.Range("Followup")
        Dim followupDateTimePicker As _
            Microsoft.Office.Tools.Excel.Controls.DateTimePicker = _
        dynamicWorksheetView.Controls.AddDateTimePicker _
            (followupDateRange, "Followup" & controlCounter)
        followupDateTimePicker.Width = 150
        If followupDateRange.Value Is Nothing Then
            'If no date is specified, default to today
            followupDateTimePicker.Value = Date.Today
            followupDateRange.Value = Date.Today
        Else
            'Use existing date if it exists
            followupDateTimePicker.Value = followupDateRange.Value
        End If
        AddHandler followupDateTimePicker.ValueChanged, _
            AddressOf FollowupDateTimePicker_ValueChanged

        'CREATE OpportunityName Change NamedRanged control
        Dim opportunityNameRange As Excel.Range = _
           sheet.Range("OpportunityName")
        Dim opportunityNamedRangeView As _
            Microsoft.Office.Tools.Excel.NamedRange = _
        dynamicWorksheetView.Controls.AddNamedRange( _
            opportunityNameRange, "OpportunityNamedRangeView" & _
            controlCounter)
        opportunityNameRange.Value = sheet.Name
        AddHandler opportunityNamedRangeView.Change, _
            AddressOf OpportunityName_Change

       'CREATE TrackerBar control
        Dim trackerBarRange As Excel.Range = sheet.Range("StatusTaskBar")
        Dim tb As New Windows.Forms.TrackBar
        tb.TickStyle = TickStyle.BottomRight
        tb.Maximum = 4
        dynamicWorksheetView.Controls.AddControl(tb, _
            trackerBarRange, "trackbar" & controlCounter)
        tb.Value = trackerBarRange.Value
        tb.Width = 125
        AddHandler tb.Scroll, AddressOf TrackBar_Scroll

    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        controlCounter += 1
    End Try
End Sub

AddDynamicControlsToLeadWorksheet takes the new worksheet created by CreateNewLeadWorksheet. It then calls GetExtendedWorksheet to get back the Worksheet object of the new worksheet. With a new Worksheet object, AddDynamicControlsToLeadWorksheet can dynamically add controls to the worksheet.

AddDynamicControlsToLeadWorksheet adds the following controls to the worksheet:

  • DateTimePicker control. Holds the initial contact date.

  • DateTimePicker control. Holds the follow-up contact date.

  • TrackerBar control. Sets the status of the lead.

  • NamedRange control. Monitors cell change events.

The first control added to the worksheet is a DateTimePicker control, which displays the initial contact date. The function creates the initial date DateTimePicker control, places it in a range called InitialContact, and then sets its value. On a new worksheet, the initial date is set to today's date. When the code calls AddDynamicControlsToLeadWorksheet on an existing worksheet, it pulls the initial contact date from the InitialContact range in the worksheet.

After the code creates the control and sets its properties, it hooks up the ValueChanged event of the DateTimePicker control to the initialDateTimePicker_ValueChanged procedure using AddHandler and AddressOf, as shown in this example:

AddHandler initialDateTimePicker.ValueChanged, _
AddressOf initialDateTimePicker_ValueChanged

The following code shows the initialDateTimePicker_ValueChanged function:

Private Sub initialDateTimePicker_ValueChanged( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs)
    Dim initialDateDatePicker As _
    Microsoft.Office.Tools.Excel.Controls.DateTimePicker = sender
    Dim initialDateRange As Excel.Range = _
    Globals.ThisWorkbook.ActiveSheet.Range("InitialContact")
    Try
        initialDateRange.Value = initialDateDatePicker.Value
    Catch ex As System.Runtime.InteropServices.COMException
        'The value cannot be changed right now, reset the date picker to the original date
        initialDateDatePicker.Value = initialDateRange.Value
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub 

When the ValueChanged event fires on the InitialDateTimePicker control, the sender object is the DateTimePicker control that the user clicked. In this event, you can use Globals.ThisWorkbook.ActiveSheet to interact with the current worksheet. The initialDateTimePicker_ValueChanged event procedure stores the value of the DateTimePicker in the cell on which the DateTimePicker is placed.

Depending on your solution, you can choose where to store information related to the controls on the worksheet. You might choose to store values in a separate data file that you can reference later. Alternatively, you might choose to use a hidden workbook to store data that needs to persist between Excel user sessions. In my experience, the best choice for most scenarios is storing the value of the control in the cell that it covers. This gives you an easy way to store the data and it is easy to reference from anywhere else in Excel using standard Excel functions because the data is in a cell. Also, because the control sits on top of the cell, the stored value is not visible to the user.

Another benefit of storing the controls' values in underlying cells is that those values persist when the user saves the workbook. You can use those saved values to restore the state of the controls when the user reopens the workbook.

Where Did My Controls Go?

All controls created in the Visual Studio 2005 designer persist between user sessions of a workbook. In other words, when a user closes a workbook, and then reopens the workbook later, the controls are still there. However, if a control is created dynamically at run time, as in the AddDynamicControlsToLeadWorksheet function, the controls do not persist. The same problem exists in Windows Forms development. Dynamic controls do not persist.

As the developer, you must restore the controls. To do this, the Sales Lead Tracking solution uses the Sheet1_Startup event, which fires each time a user opens the workbook. The Sheet1_Startup event examines each of the worksheets and adds back the dynamic controls.

Private Sub Sheet1_Startup( ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles Me.Startup
    'Make sure the template and configuration worksheets are hidden
    ShowHiddenSheets(False)
    Try
        'Initialize All Worksheets with dynamic controls by iterating
        'through each worksheet. If it is based on LeadTemplate, 
        'initialize all the controls. Note: The progress bar is also
        'made visible during this process and then removed.
        Me.Select()
        If Globals.ThisWorkbook.Worksheets.Count > 2 Then
            lblUpdatingWorkbook.Visible = True
            pb.Visible = True
            pb.Step = pb.Maximum / (Globals.ThisWorkbook.Worksheets.Count - 1)
            For Each ws As Excel.Worksheet In Globals.ThisWorkbook.Worksheets
                pb.Increment(pb.Step)
                Globals.ThisWorkbook.Application.ScreenUpdating = False
                Try
                    'Determine that this is one of the Lead worksheets, 
                    'but dont modify our base template
                    If ws.CustomProperties.Count > 0 Then
                       Dim cp As Excel.CustomProperty = ws.CustomProperties.Item(1)
                       If cp.Value = True Then
                           AddDynamicControlsToLeadWorksheet(ws)
                       End If
                    End If
                Catch ex As Exception
                End Try
                Me.Select()
                Globals.ThisWorkbook.Application.ScreenUpdating = True
            Next
        End If
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        pb.Visible = False
        lblUpdatingWorkbook.Visible = False
        Globals.ThisWorkbook.Application.ScreenUpdating = True
    End Try
End Sub

When the Sheet1_Startup event fires, the code walks through each worksheet and determines if it is based on the Lead template. If it is, the event calls AddDynamicControlsToLeadWorksheet, passing in the sheet that needs dynamically created controls. Because the original values of the controls are stored in cells on the worksheet, as each control is dynamically created, it is set to the value persisted into the worksheet.

As the document starts up, the code displays a progress bar to let the user know that the document is updating, as shown in Figure 5. If the workbook has many worksheets, it might take a while for each worksheet to populate with dynamic controls. The progress bar gives users feedback, letting them know to wait.

Figure 5. Progress bar

Progress bar

When a user first opens a workbook, he or she might notice that the controls look disabled, and that they disappear and then reappear. When a user closes a workbook and saves it, the Visual Studio 2005 Tools for Office runtime does not destroy the controls; they are saved with the document. The next time the user opens the workbook, the runtime destroys the controls, recreates them, and connects them to their event handlers. Visual Studio 2005 Tools for Office team takes this approach so that controls run reliably in Excel.

The sample code in this article is intended for instructional purposes, and should not be used in deployed solutions without modifications. In particular, you must consider code security.

For more information about code security, visit the Microsoft Security Developer Center.

In this article, you saw how to use managed controls. Managed controls enable you to present and navigate data in ways that Excel does not support originally. The .NET Framework includes many useful controls, but why stop there? Using the .NET Framework, you can create custom UserControls and use them in Excel.

Chris Kunicki is a longtime enthusiast of Office development and has been evangelizing Office as an important platform for building solutions for many years. As the founder of OfficeZealot.com, a leading Web site on Office solutions development, Chris builds enterprise solutions, designs tools for developers, delivers presentations, and writes extensively on the topic of Microsoft Office. Find out more about Chris and Office at www.OfficeZealot.com or send him an e-mail message at chris@officezealot.com.

Show:
© 2014 Microsoft