Visual Studio 2005 Tools for Office Hands-on Labs for Excel 2003

  • Article

 

Microsoft Corporation

June 2005

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

Summary: Use these Hands-on Labs to gain experience in building an asset allocation application and a debt consolidation application using Microsoft Visual Studio 2005 Tools for the Microsoft Office System Beta 2 and Microsoft Office Excel 2003. (18 printed pages)

Note   This article is pre-release documentation and is subject to change in future releases. Beta versions of Microsoft Visual Studio 2005 Tools for the Microsoft Office System are included with the beta versions of Microsoft Visual Studio 2005.

Download VSTO2005ExcelLabs.msi.

Contents

Setting Up the Labs
Lab 1-Connecting Data and Controls in an Excel Workbook
Lab 2-Using Controls in the Actions Pane
Lab 3-Connecting Data in an Excel Workbook and Actions Pane
Conclusion
Additional Resources

Setting Up the Labs

To work through the lab exercises, you must install:

  • Microsoft Windows 2000 or later
  • Microsoft Visual Studio 2005 Beta 2
  • Microsoft Visual Studio 2005 Tools for the Microsoft Office System Beta 2 (included with Visual Studio 2005 Beta 2)
  • Microsoft Office Professional Edition 2003 with Microsoft Excel Analysis ToolPak add-in installed
  • Microsoft SQL Server 2000 (or later) or MSDE

Note   When applicable, instructions in the labs refer to files by a full path; it is assumed that the download files are extracted to \My Documents\Visual Studio 2005\Projects. The files needed to complete the labs are in a subfolder named VSTO2005 Excel Labs\Starter Files. You can find a completed version of the labs in the VSTO2005 Excel Labs\Completed Labs subfolder.

Lab 1—Connecting Data and Controls in an Excel Workbook

This lab demonstrates how to connect to a SQL Server data source for the purpose of binding controls in a Microsoft Office Excel workbook to the data source. You also use Windows Forms controls on the workbook to navigate records in the data source.

Estimated time to complete:

  • Exercise 1: Connecting and Binding Data—15 minutes
  • Exercise 2: Using Windows Forms Controls—15 minutes

Exercise 1: Connecting and Binding Data

To create an Excel Workbook project

  1. On the File menu, click New, and then click Project.

  2. In the list of Project Types, expand Visual Basic and click Office.

  3. Select Excel Workbook in the list of project Templates.

  4. Type AssetAllocations in the project Name box, and type a location in the Location box, such as C:\VSTO2005\Labs. (If there is no Location box, the Save Project dialog box appears in Step 11 instead.)

  5. Click OK. The Visual Studio Tools for Office Project Wizard appears.

  6. In the wizard, click Copy an existing document.

  7. Click Browse.

  8. Navigate to the following directory and then click Open:

    \My Documents\Visual Studio 2005\Projects\VSTO2005 Excel Labs\Starter Files\Asset Allocations.xls

  9. Click OK. If this is your first Visual Studio 2005 Tools for Office project for Excel, you may see the dialog box shown in Figure 1. If you do, click OK to acknowledge and close the dialog box.

    Access to the Visual Basic for Applications project system is disabled by default (click to see larger image)

    Figure 1. Access to the Visual Basic for Applications project system is disabled by default (click picture to see larger image)

  10. On the File menu, click Save All.

  11. If you did not have a Location field in Step 4, the Save Project dialog box appears. In the project Location box, type a location, such as C:\VSTO2005\Labs, and click Save.

To create the sample database by using a SQL script

This lab uses a SQL Server database that is included in the starter files. You use a SQL script to add the sample database to your instance of SQL Server. The name of the new database is VSTO2005Lab.

  1. On the Start menu, click Run. Type cmd and click OK. A command window opens.

  2. Go to the folder where the starter files reside, \My Documents\Visual Studio 2005\Projects\VSTO2005 Excel Labs\Starter Files.

  3. Edit the file VSTO2005ExcelLab.sql to change the path to the backup database. For example:

    USE master
GO
RESTORE DATABASE VSTO2005Lab
FROM DISK = 'C:\Documents and Settings\username\My Documents\Visual 
Studio 2005\Projects\VSTO2005 Excel Labs\Starter 
Files\VSTO2005ExcelLab_DB.bak'
GO

    Note   If SQL Server is installed on a drive other than C, edit the SQL script to move the logical data base files to a different drive. For example, if SQL Server is installed on drive D, add the following to the RESTORE statement:

    WITH MOVE 'VSTO2Lab_Data' TO 'D:\Program Files\Microsoft SQL Server\ MSSQL\Data\VSTO2Lab_Data.MDF',

    MOVE 'VSTO2Lab_Log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL\ Data\VSTO2Lab_Log.LDF'

  4. Execute the batch procedure.

    osql.exe –n -E -i VSTO2005ExcelLab.sql

    A message appears, stating that the database is successfully restored.

  5. Close the command window.

To add a data source to your project

  1. On the Data menu, select Show Data Sources. The Data Sources window appears.

  2. Click Add New Data Source in the Data Sources window. You see the Data Source Configuration Wizard.

  3. Select Database for the data source type and click Next.

  4. Click New Connection. If you have not created a data connection before, the Choose Data Source dialog box appears. If you have created a data connection, you see the Add Connection dialog box shown in Figure 3 instead, and you can skip to Step 7.

  5. In the Choose Data Source dialog box, select Microsoft SQL Server, as shown in Figure 2.

    Choose Data Source dialog box

    Figure 2. Choose Data Source dialog box

  6. Click Continue. You see the Add Connection dialog box shown in Figure 3.

    The Add Connection dialog box (Click to see larger image)

    Figure 3. The Add Connection dialog box (click picture to see larger image)

  7. Create the connection:

    1. If the value in the Data source box is not Microsoft SQL Server (SqlClient), click Change and choose the SQL Server data source.
    2. In the Server name box, type (local).
    3. Select Use Windows Authentication for the authentication mode.
    4. In the Connect to a database section, select VSTO2005Lab in the Select or enter a database name list.
    5. Click Test Connection to confirm that the connection is valid and then click OK to add the connection. The new connection has the default name YourServerName.VSTO2005Lab.dbo.

  8. Click Next.

  9. Save the connection using the default name, VSTO2005LabConnectionString.

  10. Click Next.

  11. In the list of database objects, select the Tables check box and the Views check box and click Finish.

To add a relation to the data source

  1. In the Data Sources window, select VSTOLab2005DataSet and click Edit DataSet with Designer (at the top of the Data Sources window).

  2. On the Data menu, point to Add and then click Relation. The Relation dialog box appears.

  3. Set the following fields for the new relation:

    • Name is Customers_PortfolioView
    • Parent Table is Customers
    • Child Table is PortfolioView
    • Key Column is CustomerID
    • Foreign Key Column is CustomerID

    The completed dialog box looks like Figure 4.

    Completed Relation dialog box (click to see larger image)

    Figure 4. Completed Relation dialog box (click picture to see larger image)

  4. Click OK to add the new relation.

  5. The VSTO2005LabDataSet designer window now looks like Figure 5.

    New relation between Customers and PortfolioView (click to see larger image)

    Figure 5. New relation between Customers and PortfolioView (click picture to see larger image)

To bind NamedRange controls to fields in the data source

  1. In Solution Explorer, right-click Sheet1.vb and then click View Designer on the shortcut menu.
  2. In the Data Sources window, expand the Customers table.
  3. Click CustomerID under the Customers table and drag it onto cell B2 of Sheet1.
  4. Click FirstName under the Customers table and drag it onto cell E2 of Sheet1.
  5. Click LastName under the Customers table and drag it onto cell E3 of Sheet1.
  6. Click BirthDate under the Customers table. Click the arrow to the right of the BirthDate field and then click NamedRange in the list. Click BirthDate and drag it onto cell E4 of Sheet1.
  7. Notice that the formula for cell E5 is already set to calculate the customer's age.

To complex-bind a ListObject control to fields in the data source

  1. In the Data Sources window, click PortfolioView and drag it onto cell A7 of Sheet1. A ListObject named PortfolioViewListObject is created. It contains one column for each field in PortfolioView. Leave PortfolioViewListObject selected.

    Note   When you drag and drop complex elements from the Data Sources window onto a worksheet, you lose the formatting of the cells in the new ListObject on the worksheet. Lab 2, Exercise 2, explains how to manually fix this by setting the DataBoundFormat property of the ListObject.

  2. With the new ListObject selected, on the Data menu, point to Microsoft Office Excel Data, point to Filter, and then click AutoFilter to remove the AutoFilter lists from the ListObject.

  3. In the Properties window, click the drop-down arrow next to the DataSource property of the PortfolioViewListObject ListObject. Expand CustomersBindingSource in the list and then click Customers_PortfolioView.

  4. The Asset Allocations workbook uses named ranges in the ListObject for calculations. Name the Sheet1 range B7:B8 Symbol by following these steps:

    • Select the range B7:B8.
    • In the Name box shown in Figure 6, type Symbol and press ENTER.

    Use the Name box to name a range

    Figure 6. Use the Name box to name a range

  5. Repeat the steps in Step 4, to name the ranges described in Table 1.

    Table 1. Named ranges needed for the Asset Allocation workbook

    Name Range
    Symbol Sheet1!$B$7:$B$8
    Class Sheet1!$D$7:$D$8
    LastPrice Sheet1!$G$7:$G$8
    Shares Sheet1!$H$7:$H$8
    Amount Sheet1!$I$7:$I$8

Checkpoint

  1. On the File menu, click Save All.

  2. On the Debug menu, click Start to build and run the project. The workbook Asset Allocations.xls opens in Excel, as shown in Figure 7.

    Ranges in the workbook are bound to data in the SQL Server tables (click to see larger image)

    Figure 7. Ranges in the workbook are bound to data in the SQL Server tables (click picture to see larger image)

  3. The data for the first record in the Customers table is displayed in the bound NamedRange controls; additionally, the corresponding details from the PortfolioView view appear in the ListObject control.

  4. Exit Excel without saving the workbook.

Exercise 2: Using Windows Forms Controls

To add Windows Forms controls to the workbook for navigating the data source

  1. If the Toolbox window is not visible, click the View menu and then click Toolbox.

  2. Select a Button control in the Toolbox and drag it to cell A4 of Sheet1. Button1 is added to the worksheet.

  3. Select another Button control in the Toolbox and drag it to cell B4 of Sheet1. Button2 is added to the worksheet.

  4. Use the Properties window to change the properties of the buttons, as shown in Table 2.

    Table 2. Property settings for button controls

    Control Property Value
    Button1 (Name) btnPrevious
      Text Previous
    Button2 (Name) btnNext
      Text Next

  5. In Solution Explorer, right-click Sheet1.vb and then click View Code on the shortcut menu.

  6. The designer automatically created a class named Sheet1 for the worksheet and added code to the Startup event for Sheet1. The designer added this code when you bound the NamedRange and ListObject controls to fields in the data source.

    Private Sub Sheet1_Startup(ByVal sender As Object, _
   ByVal e As System.EventArgs) Handles Me.Startup
    'TODO: Delete this line of code to remove the default AutoFill
    'for 'VSTO2005LabDataSet.PortfolioView'.
    If Me.NeedsFill("VSTO2005LabDataSet") Then
        Me.PortfolioViewTableAdapter.Fill( _
          Me.VSTO2005LabDataSet.PortfolioView)
    End If
    'TODO: Delete this line of code to remove the default AutoFill 
    'for 'VSTO2005LabDataSet.Customers'.
    If Me.NeedsFill("VSTO2005LabDataSet") Then
        Me.CustomersTableAdapter.Fill(Me.VSTO2005LabDataSet.Customers)
    End If
End Sub

  7. In the Class Name list, click btnPrevious. In the Method Name list, click Click.

  8. Add code to the Click event of btnPrevious to navigate the data source.

    Private Sub btnPrevious_Click(ByVal sender As Object, _
  ByVal e As System.EventArgs) Handles btnPrevious.Click
    'Move to the previous record.
    Me.CustomersBindingSource.MovePrevious()
End Sub

  9. Follow the same process to add code to the Click event of btnNext.

    Private Sub btnNext_Click(ByVal sender As Object, _
  ByVal e As System.EventArgs) Handles btnNext.Click
    'Move to the next record.
    Me.CustomersBindingSource.MoveNext()
End Sub

Checkpoint

  1. On the File menu, click Save All.
  2. On the Debug menu, click Start to build and run the project. The Asset Allocations workbook opens in Excel.
  3. Click the buttons to move between the records in the data source. Notice that the details in the ListObject update when the bound fields for the parent table (Customers) change.
  4. Exit Excel without saving changes.

Lab 2—Using Controls in the Actions Pane

In this lab, you create an actions pane for an Excel solution using a Windows Forms control and a user control that you also create.

Note   This lab depends upon formulas from the Excel Analysis ToolPak-VBA add-in.

Estimated time to complete:

  • Exercise 1: Adding a Windows Forms Control to the Actions Pane—5 minutes
  • Exercise 2: Adding a User Control to the Actions Pane—5 minutes

Exercise 1: Adding a Windows Forms Control to the Actions Pane

To create an Excel Workbook project

  1. On the File menu, click New, and then click Project.

  2. In the list of Project Types, expand Visual Basic and click Office.

  3. Select Excel Workbook in the list of project Templates.

  4. Type DebtConsolidation in the project Name box, and type a location in the Location box, such as C:\VSTO2005\Labs. (If no Location box appears, the Save dialog box appears in Step 10 instead.)

  5. Click OK. The Visual Studio Tools for Office Project Wizard appears.

  6. In the wizard, click Copy an existing document.

  7. Click Browse.

  8. Navigate to the following folder, click Debt Consolidation.xls, click Open, and then click OK:

    \My Documents\Visual Studio 2005\Projects\VSTO2005 Excel Labs\Starter Files

  9. On the File menu, click Save All.

  10. If a Location field did not appear in Step 4, a Save dialog box appears. In the project Location box, type a location, such as C:\VSTO2005\Labs, and click Save.

To set the size and position for the actions pane

  1. In Solution Explorer, right-click ThisWorkbook.vb and then click View Code. The code module for the workbook appears. Observe that the workbook code-behind is encapsulated in a class named ThisWorkbook and that event handlers for Startup and ShutDown already exist.

  2. Add code to the ThisWorkbook_Startup event to size and position the actions pane when the workbook loads and to initialize the worksheet view.

    Private Sub ThisWorkbook_Startup(ByVal sender As Object, _
  ByVal e As System.EventArgs) Handles Me.Startup

    With Me.Application.CommandBars("Task Pane")
        .Width = 300
        .Position = Microsoft.Office.Core.MsoBarPosition.msoBarLeft
    End With
    Dim labelControl As New Label
    labelControl.Text = "TO DO: Add Controls Here"
    Me.ActionsPane.Controls.Add(labelControl)
    Globals.Sheet2.Select()
    Me.Application.ActiveWindow.DisplayHeadings = False
End Sub

Checkpoint

  1. On the File menu, click Save All.

  2. On the Debug menu, click Start to build and run the project. The workbook titled Debt Consolidation.xls opens in Excel.

  3. The actions pane is displayed with a label, as shown in Figure 8.

    The code adds a label to the actions pane (click to see larger image)

    Figure 8. The code adds a label to the actions pane (click picture to see larger image)

  4. Close the workbook without saving changes and exit Excel.

Exercise 2: Adding a User Control to the Actions Pane

To create a user control

  1. In Solution Explorer, right-click the DebtConsolidation project, point to Add on the shortcut menu, and then click New Item. The Add New Item dialog box appears.

  2. Select the User Control template.

  3. Type CustomerProfile in the Name box and click Add.

  4. Set properties for the CustomerProfile user control by referring to the values in Table 3.

    Table 3. Property settings for the CustomerProfile user control

    Property Value
    BackColor InactiveCaptionText
    ForeColor Desktop
    Size 300, 600

To add the user control to the actions pane

  1. In Solution Explorer, right-click Thisworkbook.vb and then click View Code on the shortcut menu.

  2. Add a new member variable for an instance of a CustomerProfile control to the ThisWorkbook class.

    Friend WithEvents profileControl As CustomerProfile

  3. Append the following code to ThisWorkbook_Startup to add an instance of the CustomerProfile control to the actions pane.

    profileControl = New CustomerProfile
Me.ActionsPane.Controls.Add(profileControl)

Checkpoint

  1. On the File menu, click Save All.

  2. On the Debug menu, click Start to build and run the project. The workbook titled Debt Consolidation opens in Excel.

  3. The actions pane now contains two controls, a Label control and a CustomerProfile user control.

    Note   You add controls to the CustomerProfile control in another lab. For now, you see only the user control surface in the actions pane.

  4. Close the workbook without saving changes and exit Excel.

Lab 3—Connecting Data in an Excel Workbook and Actions Pane

In this lab, you connect to a SQL Server data source, bind controls in both the workbook and the actions pane to database objects, and navigate records in the data source.

Estimated time to complete:

  • Exercise 1: Setting Up a Connection and Adding a Data Source—10 minutes
  • Exercise 2: Connecting, Binding, and Navigating Data—40 minutes

This lab uses the DebtConsolidation project that you created in Lab 2.

Exercise 1: Setting Up a Connection and Adding a Data Source

To create the sample database using a SQL script

  1. On the Start menu, click Run. Type cmd and click OK. A command window opens.

  2. Navigate to the folder where the starter files reside, \My Documents\Visual Studio 2005\Projects\VSTO2005 Excel Labs\Starter Files.

  3. Edit the file DebtConsolidation.sql to change the path to the backup database. For example, change username in the following script to your user name.

    USE master
GO
RESTORE DATABASE VSTO2005Lab
FROM DISK = 'C:\Documents and Settings\username\My Documents\Visual 
Studio 2005\Projects\VSTO2005 Excel Labs\Starter 
Files\VSTO2005DebtConsolidation_DB.bak'
GO

    Note   If SQL Server is installed on a drive other than C, edit the SQL script to move the logical data base files to a different drive. For example, if SQL Server is installed on drive D, add the following to the RESTORE statement:

    WITH MOVE 'VSTO2DebtConsolidation_Data' TO 'D:\Program Files\ Microsoft SQL Server\MSSQL\Data\VSTO2DebtConsolidation_Data.MDF',

    MOVE 'VSTO2DebtConsolidation_Log' TO 'D:\Program Files\ Microsoft SQL Server\MSSQL\Data\VSTO2DebtConsolidation_Log.LDF'

  4. Execute the batch procedure.

    osql.exe –n -E -i DebtConsolidation.sql

    A message appears, stating that the database is successfully restored.

  5. Close the command window.

To add a data source to your project

  1. On the Data menu, click Show Data Sources. The Data Sources window appears.

  2. In the Data Sources window, click Add New Data Source. The Data Source Configuration Wizard appears.

  3. Select Database for the data source type and click Next.

  4. Click New Connection. Because you already created a data connection in Lab 1, you see the Add Connection dialog box shown in Figure 9.

    The Add Connection dialog box (click to see larger image)

    Figure 9. The Add Connection dialog box (click picture to see larger image)

  5. Create the connection:

    1. If the Data Source is not Microsoft SQL Server (SqlClient), click Change and choose the SQL Server data source.
    2. In the Server Name box, type (local).
    3. Select Use Windows Authentication for the authentication mode.
    4. In the Connect to a database section, select VSTO2005DebtConsolidation in the Select or enter a database name list.
    5. Click Test Connection to confirm that the connection is valid and then click OK to add the new connection. The new connection has the default name YourServerName.VSTO2005DebtConsolidation.dbo.

  6. Click Next.

  7. Save the connection using the default name, VSTO2005DebtConsolidationConnectionString.

  8. Click Next.

  9. In the list of database objects, select the Tables check box and then click Finish.

  10. Click VSTO2005DebtConsolidationDataSet in the Data Sources window and then click Edit DataSet with Designer at the top of the Data Sources window. Observe that a relation named FK_LoanData_NewCustomers already exists between the LoanData and NewCustomers tables.

Exercise 2: Connecting, Binding, and Navigating Data

To select controls to use for binding NewCustomers fields

  1. In Solution Explorer, right-click CustomerProfile.vb and then click View Designer on the shortcut menu.
  2. On the Data menu, click Show Data Sources to display the Data Sources window.
  3. Select NewCustomers in the Data Sources window.
  4. Click the drop-down arrow next to NewCustomers and click Details.
  5. Expand NewCustomers.
  6. Click to select the ProposedRate field, click the arrow next to ProposedRate, and then click Customize. The Options dialog box appears.
  7. Select NumericUpDown in the Associated controls list (if it is not already selected) and click OK.
  8. Click the drop-down arrow next to ProposedRate, and then click NumericUpDown.
  9. Click to select the MinimumRate field, click the arrow next to MinimumRate, and then click None.

To add controls that are bound to the data source to the CustomerProfile component

  1. Drag NewCustomers from the Data Sources window and drop it at the upper-left corner of the user control design surface.

    Observe that the designer automatically generates Label controls, bound TextBox controls, and a bound NumericUpdown control for the fields in the NewCustomers table. The designer also adds a BindingNavigator control for navigating the records in the data source at run time.

  2. In the Data Sources window, select the LoanData field of NewCustomers and drag it to the user control. Drop it under the existing NewCustomers fields.

    Observe that the designer adds a DataGridView control, named LoanDataDataGridView, to the user control.

  3. Select the LoanDataDataGridView control on the user control, and in the Properties window, select the drop-down next to the Datasource property.

  4. In the drop-down list, expand NewCustomersBindingSource and click FK_LoanData_NewCustomers.

    Observe that the designer adds a new binding source, named FK_LoanData_NewCustomersBindingSource, to the component tray. The LoanDataDataGridView control is bound to this new binding source.

To adjust the data grid display properties

  1. Right-click the LoanDataDataGridView control and then click Edit Columns.

  2. Remove NewCustomerID, LoanNumber, LoanAmount, and LoanRate from the list of selected columns by clicking the Remove button.

  3. Select LoanName in the list of selected columns. Set the Width property to 150 and the ReadOnly property to True.

  4. Select Include in the list of selected columns and click the Move Up button. Set the Width property to 50 and the ReadOnly property to True.

  5. Click OK to close the Edit Columns dialog box.

    Note   At this point, Include and LoanName should be the only two columns in LoanDataDataGridView.

  6. Set the properties for LoanDataDataGridView, as shown in Table 4.

    Table 4. Property settings for the data grid

    Property Value
    AllowUserToAddRows False
    AllowUserToDeleteRows False
    RowHeadersVisible False
    Size 200, 175

To set NumericUpDown control properties and add data bindings

  1. Select the ProposedRateNumericUpDown control.
  2. Set the DecimalPlaces property to 1, the Increment property to 0.5, and the Maximum property to 24.
  3. Expand the (DataBindings) property and click the ellipsis (...) next to (Advanced). The Formatting and Advanced Binding dialog box appears.
  4. Select Minimum in the Property list. Click the drop-down arrow for the Binding list. In the list, expand NewCustomersBindingSource and select MinimumRate.
  5. Click OK to close the Formatting and Advanced Binding dialog box.
  6. Select NewCustomersBindingNavigator in the component tray.
  7. In the Properties window, expand the BindingSource properties and set the AllowNew property to False.

To fill the table adapters when the component loads

  1. On the View menu, click Code.

  2. Select (CustomerProfile Events) in the Class Name list and Load in the Method Name list.

  3. Add code to fill the table adapters.

    Private Sub CustomerProfile_Load(ByVal sender As Object, _
     ByVal e As System.EventArgs) Handles Me.Load
     Me.LoanDataTableAdapter.Fill( _
         Me.VSTO2005DebtConsolidationDataSet.LoanData)
     Me.NewCustomersTableAdapter.Fill( _
         Me.VSTO2005DebtConsolidationDataSet.NewCustomers)
End Sub

To toggle the Include field when a DataGridView row is clicked

  1. Select LoanDataDataGridView in the Class Name list and CellContentClick in the Method Name list.

  2. Add code to toggle the Include field when the row is clicked.

    Private Sub LoanDataDataGridView_CellContentClick( _
    ByVal sender As Object, ByVal e As _
    System.Windows.Forms.DataGridViewCellEventArgs) _
    Handles LoanDataDataGridView.CellContentClick
    Try
        Dim r As Integer = Me.LoanDataDataGridView.CurrentCell.RowIndex
        Dim rv As DataRowView = DirectCast( _
            Me.LoanDataDataGridView.Rows(r).DataBoundItem, DataRowView)
        rv("Include") = Not (rv("Include"))
        rv.EndEdit()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

To eliminate the placeholder label from the actions pane

  1. In Solution Explorer, right-click ThisWorkbook.vb and then click View Code.

  2. In the ThisWorkbook_Startup procedure, comment out the three lines used to define and place the label on the actions pane.

    'Dim labelControl As New Label
'labelControl.Text = "TO DO: Add Controls Here"
'Me.ActionsPane.Controls.Add(labelControl)

Checkpoint

  1. On the File menu, click Save All.

  2. On the Debug menu, click Start to build and run the project. The Debt Consolidation workbook opens in Excel.

  3. The records from the data source appear in the actions pane, as illustrated in Figure 10. The first record is for the customer Nancy Davolio.

    The actions pane with bound data

    Figure 10. The actions pane with bound data

  4. Click one or more rows in the DataGridView control to select or clear the check boxes.

  5. Change the ProposedRate.

  6. Click Move next on the BindingNavigator control to move to the next record. The next record corresponds to the customer Andrew Fuller.

  7. Click Move previous on the BindingNavigator control to move to the previous record. The data for the customer Nancy Davolio appears. Notice that your changes to the bound DataGridView and NumericUpDown controls are restored (in other words, your changes persisted in the in-memory dataset).

  8. Close the workbook without saving changes and exit Excel.

To update a named range based on changes in the actions pane

  1. In Solution Explorer, right-click CustomerProfile.vb and then click View Code on the shortcut menu.

  2. Add code to the CustomerProfile class to raise an event named RateChanged when the value in the bound NumericUpDown control changes.

    Public Event RateChanged(ByVal NewRate As Double)

Private Sub ProposedRateNumericUpDown_ValueChanged( _
  ByVal sender As Object, ByVal e As System.EventArgs) _
  Handles ProposedRateNumericUpDown.ValueChanged
    RaiseEvent RateChanged(ProposedRateNumericUpDown.Value * 0.01)
End Sub

  3. In Solution Explorer, right-click Thisworkbook.vb and then click View Code on the shortcut menu.

  4. Add an event handler for the RateChanged event of the profileControl control, to add the new rate to the Sheet2 worksheet in the InterestRate named range.

    Private Sub profileControl_RateChanged(ByVal NewRate As Double) _
   Handles profileControl.RateChanged
      Globals.Sheet2.InterestRate.Value2 = NewRate
End Sub

To bind an Excel ListObject to the data source

  1. In Solution Explorer, right-click CustomerProfile.vb and then click View Code on the shortcut menu.

  2. Add a new read-only property named Connector that returns the BindingSource that the CustomerProfile control is using for the current data source.

    Public ReadOnly Property Connector() As BindingSource
    Get
        Return Me.NewCustomersBindingSource
    End Get
End Property

  3. In Solution Explorer, right-click Sheet2.vb and then click View Designer on the shortcut menu.

  4. On the Data menu, point to Microsoft Office Excel Data, point to List, and then click Create List. The Create List dialog box appears.

  5. Type =A12:F13 for the range, select My list has headers, and click OK.

  6. On the Data menu, point to Microsoft Office Excel Data, point to Filter, and then click AutoFilter to remove the Filter controls from the list.

  7. On the Data menu, point to Microsoft Office Excel Data, point to List, and then click Hide Border of Inactive Lists.

  8. In Solution Explorer, right-click ThisWorkbook.vb and then click View Code.

  9. Append the following code to ThisWorkbook_Startup.

    With Globals.Sheet2.List1
    .AutoSetDataBoundColumnHeaders = False
    .DataBoundFormat = Excel.XlRangeAutoFormat.xlRangeAutoFormatNone
    .SetDataBinding(profileControl.Connector, _
        "FK_LoanData_NewCustomers")
End With

    This binds the ListObject on Sheet2 to the same BindingSource used by the CustomerProfile control in the actions pane, and does not change the formatting of the ListObject.

Checkpoint

  1. On the File menu, click Save All.

  2. On the Debug menu, click Start to build and run the project. The workbook titled Debt Consolidation opens in Excel.

  3. Notice that the data in the actions pane now appears in the CallSheet worksheet, as shown in Figure 11.

  4. Change the ProposedRate and observe that your changes reflect in the InterestRate named range on the worksheet.

  5. Select or clear check boxes in the Include column of the DataGridView and observe that your changes reflect in the ListObject control on the worksheet.

  6. Close the workbook without saving changes and exit Excel.

    The data now appears in the worksheet and the actions pane (click to see larger image)

    Figure 11. The data now appears in the worksheet and the actions pane (click picture to see larger image)

Conclusion

In these labs, you learned how to create an asset allocation application and a debt consolidation application using Microsoft Visual Studio 2005 Tools for the Microsoft Office System Beta 2 and Microsoft Office Excel 2003. You learned how to use Windows Forms controls, host controls, the actions pane, and data binding.

Additional Resources

For more information, see the following resources: