Creating Toolbar Buttons

In Chapter 7 (Customizing Excel with VSTO), you learned how to convert a simple VBA macro to Visual Basic 2005 code. In this section we modify that code so that bold formatting toggles on and off in the Click event handler of a button that we add to the Standard toolbar. This code is similar to the code shown in Chapter 6 (Customizing Word with VSTO), but here it is specific to Excel.

We further extend this code example to show you how to add a custom icon to the button. First, you'll write code in the Startup event handler of Sheetl that adds the control to the toolbar. As shown in Listing 8.1, the code checks to see whether a control with the tag "FormatButton" already exists. If it does, the existing control is deleted before the new one is added. Without this code, a new button would be added to Excel's

Formatting toolbar each time the Startup event handler is called (each time the workbook is opened and Sheet1 is initialized).

Listing 8.1. Modifying a recorded macro in Excel ' Visual Basic 2005

Dim commandBar As Office.CommandBar

WithEvents formatButton As Office.CommandBarButton

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

' If the button already exists, remove it. formatButton = Application.CommandBars("Formatting") _ .FindControl(Tag:="FormatButton")

If formatButton IsNot Nothing Then formatButton.Delete()

End If

Catch Ex As Exception

MsgBox(Ex.Message)

End Try commandBar = Application.CommandBars("Formatting") formatButton = CType(commandBar.Controls.Add(1), _ Office.CommandBarButton)

With formatButton

.Caption = "Bold and Italic" .Tag = "FormatButton" .FaceId = 34 End With

You should always set a value in the Tag property of the control so that Excel (or other Office application) can distinguish between multiple controls you've added to the toolbar. Next, in the button's Click event handler you add the code for formatting the selected cells as bold and italic. In this example, the code is handled by the formatButton method. The method takes two parameters: CommandBarButton, which represents the button being clicked; and a Boolean variable that enables you to cancel the button's default action (in the case that you're modifying an existing button). Add the code in Listing 8.2 to the Sheetl class.

Listing 8.2. Handling the Click event of a toolbar button in VSTO ' Visual Basic 2005

Private Sub formatButton_Click(ByVal Ctrl As _

Microsoft.Office.Core.CommandBarButton, ByRef _ CancelDefault As Boolean) Handles formatButton.Click

Dim Selection As Object = Me.Application.Selection

If Selection.Font.Bold = True And _

= True Then

False = False

True = True

End If

End Sub

Selection.Font.Italic

Selection.Font.Bold = Selection.Font.Italic

Else

Selection.Font.Bold = Selection.Font.Italic

In this code example, you declare the variables for the CommandBar and CommandBarButton objects outside the Startup event handler.

This is because you must access these variables from the formatButton method. If you were to declare the variables inside the Startup event handler, the variables would fall out of scope after initialization is complete and would become eligible for garbage collection. This would prevent the button's Click event handler from being raised, and that is why you should declare variables for command bar buttons at the class level.

When you run this code, a new button is added to the Formatting toolbar. The graphic that is added to the button (faceID 34) is an ink bottle, as shown in Figure 8.1. Each time you click the button, the bold and italic formatting is either applied to or removed from the selected text, depending on its current state.

E Microsoft Excel - ExcelWorkbookl.xls

SI*!! File Edit View Insert Format lools Data Window Help Type a question for help ▼ _ ff x iQSHQifiliflkl^l&IJi J *> f ■ il lia

Arial >10 . B | I | U jjy | % > tiS i™ ft ft UJI A 4

A1 "fx Sample Text

SI*!! File Edit View Insert Format lools Data Window Help Type a question for help ▼ _ ff x iQSHQifiliflkl^l&IJi J *> f ■ il lia

Arial >10 . B | I | U jjy | % > tiS i™ ft ft UJI A 4

A1 "fx Sample Text

A

B

C

D

E

F

G

H

I

j Bold

and Italic

1

Sample T

■x:

2

3

4

5

6

Figure 8.1. Using the faceID property to identify a button graphic

Figure 8.1. Using the faceID property to identify a button graphic

0 0

Post a comment