Microsoft Office 2000 Developer

Binding an Object using the BindingCollection Object in Excel

See Also

It is possible to bind an object to data using the BindingCollection object. This example shows how to use the BindingCollection object to interact with an Excel Range object.

To bind a Range object to the BindingCollection object

1. Set a reference to the Microsoft Data Binding Collection in the References dialog box.
   
   
2. Draw an ADO Data Control on an Excel worksheet.
   
   
3. Configure the control to connect to the Northwind database. Set the RecordSource to the Products table. For more information, see Creating a Data Source Using the ADO Data Control.
   
   
4. Draw a CommandButton on the worksheet.
   
   
5. Add the following code to the button's Click event:

   Option Explicit
   ' Declare a BindingCollection object
   Private bcProducts As BindingCollection
   
   Private Sub CommandButton1_Click()
      Set bcProducts = New BindingCollection
      Set bcProducts.DataSource = ADODC1
      bcProducts.Add Range("A1"), "Value", "ProductName"
   End Sub
   

6. Click the Exit Design Mode icon, and switch back to Excel.
   
   
7. Click the button.

The Range object will now be bound to the data source using the BindingCollection object. Clicking the ADO Data Control will change the value of the cell.