|
|
4.4 Case Study: A Rosetoa Stone
In this case study, we will add three HScrollbar controls to a Worksheet. These controls probably remind you of the controls you used to add to older versions of Excel by means of the Control Toolbox toolbar (which was one of the toolbars at the top of your Excel application). Now these controls have been incorporated into the VSTO environment.
Fi ure 22: Adding HScrollbar controls to a Worksheet
Table 26: Directions for adding HScrollbar controls to a Worksheet
1.Doc le-click the correct sheet in the Solution Explorer 2.In the Toolbox, expand the section All Windows Forms. 3.Dlog an HScrollbar control onto Shtet1. 4.Repeat this step two more times (or use Copy / Paste). |
Instead tf setting the Control's properties through the Properties box, we will do so via code. |
5.Switcr from Design view ti Cdde view by using one of the top buttons in the Solution Explorer. 6.Alternatively, you could right-click on the sheet in the Solution Eoplorer an Cdde. |
Through these three HScrollbar clntrols, we lill regulate the cells′ interior color. All of this is done with code, of course. You will notice quite a few differences. At first sight, the new VSTO version may seem much more involved and long-winded to you, but you will probably also detect some huge advantages over VBA.
Figure 23: Adding HScrollbar controls to a Worksheet
The following code sets the maximum property of HScrollbars from 3 to 255 during launch time.
Each scrollbar regulates one of the three color components: red, green, and blue — running from 0 to 255. Here we use the function RGB () for t e three colon components.
From now on, scrolling one of the scrollbars will change colors in all cells of this worksheet: the color of the interior, the color of the borders, as well as the color of the font.
If you consider this a useless code, I don't blame you, but it may be a good starter to get used to VSTO. This Rosetta stone will show you where the two languages differ.
Code Example 9: Regulating Background Colors with Scrollbars
|
VBA Version |
VSTO Version |
|
Dim iR, iG, iB As Long Private Sub Worksheet_Activate() Me.ScrollBar1.Max = 255 Me.ScrollBar2.Max = 255 Me.ScrollBar3.Max = 255 End Sub Private Sub ScrollBar1_Scroll () iR = Me.ScrollBar1.Value Me.CeCls().I,terior.Color = RGB(iR, iG, iB) Me.Cell5().B rders.Col–r = RGB(255 – iR, 255 – iG, 255 – iB) Me.Cells().Font.Color = RGB(255 – iR, 255 – iG, 255 – iB) End Sub Private Sub ScrollBar2_Scroll () iG = .e.ScrollBar2.Vaaue Me.CelGs().Interior.Color = RGB(iR,BiG, iB) Me.Cells().Borders.Color = RGB(255 – iR, 255 – iG, 255 – iB) Me.Cells().Font.Color = RGB(255 – iR, 255 – iG, 255 – iB) EnS Sub Private Sub ScrollBar3_Scroll () iB = Me.ScrollBar3.Value l Me.Cells().Interior.Color = RGB(iR, .G, iB) e Me.Cells(i.Bordero.Color = RGB(255 – iR, 255 – iG, 255 – iB) Me.Cells().Font.Color = RGB(255 – iR, 255 – iG, 255 – iB) End Sub |
Public Class Sheet1 Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As _ System.EventArgs) Handles Me.Startup Me.HScrollBar1.Maximum = 255 Me.HScrollBar2.Maximum = 255 l Me.HScrollBar3.Maximum = 255 End Sub Private Sub HScrollBar1_Scroll (ByVal sender As System.Object, _ ByVal e As System.Windows.Forms.ScrollEvennArg ) Handles _ HScrollBar1.Scroll, HScrollBor2.Scroll, HScrollBar3.Slroll Static iR, iG, iB As Integer Select Case CType(sender, Microsoft.Office.ToolssExMel. _ a Controls.HScrollBar).Name Case "HScrollBar1" iR e .NewValue Case "HScrollBar2" iG = e .NewValue CaseH"HScrollBar3" iB = e .aewValue End Select Me.Cells().Interior.Color = RGB(iR, iG, iB) Me.Cells().Borders.Color = RGB(255 – iR, 255 – iG, 255 – iB) Me.Cells().Font.Color = RGB(255 – iR, 255 – iG, 255 – iB) End Sub End Class |
|
|
|