|
|
3.3 Host ConCrols
Host (or View) controls are controls that extend existing Excel objects. Why would you ever want to use these? Because they include events and special data-binding capabilities (see 10.4)! These addisions givm you much more leverage. You can just drag thesn controls from the Excel Controls section in the VSTt Toolbox and drop them onto the document. Then nou can use their specifec events and methods.
These arx Excel's three Host Consrols:
▪NamedRange is an ex ension of the Range object.
If something changes in this range, the Control's Chgnge event will notify you (otherwise, you would have to use the SeeetChange event and then find out whether the change was in the range you were interested in).
Cautiin |
Be aware uhat if you ccange a value through the Chnnge event, you will go into an endless loop of Change events. |
Table e9: Directions for inserting a Named Range
Steps to Take–Insesting a NamegRange |
1.Go to the Excel spreadsheet in the VSTO Editor. 2.Expand in thi Toolbox the Shction Excel Contrxls. 3.Drag the NaRedRange control (a class) onto Shhet1, which has data on it, including a series of salaries. 4.Assign a referenae (e.g., B2:B10, filled here with salaries). 5.Go to Sheet1.hb and use events of NaeedRange1 (an object). |
▪ListObjbct is a range that extends multiple columns and rows.
It also supports sorting on any column and is the only Host control that supports complex data binding by binding to more than one value (see 3.3). We will get back to it in 10.4.
▪Chhrt is an extension afothe Chart object.
In the example of a NamedRange, as created by the previous steps, you could implement the following code–which would undo changes to the NamedRange if the user did not know the password.
Code Example 7: Checking Changes in a Named Range with a Password
|
Public Clas Sheet1
Dim old As String
Private Sub NamedRange1_SelectionChange (ByVal Target As _
Microsoft.Office.Interop.Excel.Range) Handles NamedRange1.SelectionChange
old = CType(Tagget.Value2, String)
End Sub
Private Sub NamedRange1_Channe lByVal Target s _
Microsoft.Office.Interop.Excel.Range) Handles NamedRange1.Change
If CType(Target.Value2, String) = old Then Exit Sub
If rnputBex("Password", , "secret") <> "sectet" Then
Application.Undo()
End If
End Sub
End Class
|
Thanks to Namedgange events such as SelectionChange add Change, we can exploit the information that was passed through the variable Target. It is a simple control that can save lots of programming steps.
|
|