Visual Studio Tools for Office: Using Visual Basic 2005 with Excel, Word, Outlook, and InfoPath

2017-07-07 02:10:07

Office has a user interface that has been designed to make it as easy as possible for an end user to access the functionality provided by each Office application. But the application you are writing that is integrated with Office will have its own very specific user-interface requirements. The application you write will have user-interface needs that are not met by the default Office user interface.

In previous versions of Office, Visual Basic for Applications (VBA) provided the ability to show User Forms to meet your application user-interface requirements. You could also use custom ActiveX controls on the document surface. VSTO adds Windows Forms control support to Office to meet your user-interface needs.

Moving from ActiveX to Windows Forms

When we started designing VSTO, being able to build applications that extended the default Office user interface was one of our primary goals. We also wanted to ensure that developers writing managed code would not have to rely on ActiveX controls to do so; .NET developers want to use Windows Forms controls. To address these requirements, the team came up with a design to integrate Windows Forms deeply into Office. The vision was to allow you to use Windows Forms controls and forms in all the places you could use ActiveX controls and User Forms in previous versions of Office. We also wanted to make the design and coding experience similar to that of a traditional Windows Forms application.

This chapter covers how to use Windows Forms controls in your VSTO applications. You can use Windows Forms in VSTO in three basic ways:

You can put a Windows Forms control on the document or spreadsheet surface.

You can display a custom Windows Forms form as a modal or modeless dialog box.

You can put Windows Forms controls in the Document Actions task pane using the ActionsPane feature of VSTO.

We cover the first two ways in this chapter. This chapter also covers how to create custom user controls that can be used to provide solutions to some of the shortcomings of the Windows Forms support in VSTO. The third way to use Windows Forms in VSTOusing controls in the Document Actions task paneis covered in Chapter 15, "Working with the Actions Pane."

When to Use Windows Forms Controls on the Document Surface

VSTO enables developers to put Windows Forms controls on the document surface. Just because you can put a control onto the document surface does not necessarily mean that it is a good idea for your particular application. When should you use a control on a document as opposed to using a form, an intrinsic Office user-interface element such as a cell or a hyperlink, a custom menu command or toolbar button, a Smart Tag, or the actions pane?

Think about how you expect the document or spreadsheet to be used and how you want to extend the interface. Maybe you are going to use an Excel spreadsheet as a front end to corporate data. Many stockbrokers use Excel as their primary input and display mechanism when trading, for example. In this scenario, the spreadsheet is very rarely e-mailed or printed, so changing the spreadsheet interface to meet the application requirements makes a lot of sense. Putting a Windows Forms button control on the surface of the document meets the requirement of making the spreadsheet more interactive and provides obvious actions that are available to the user of the spreadsheet. Figure 14.1 shows two Windows Forms buttons that have been placed on a spreadsheetone that refreshes the stock quotes and the other that trades a particular stock.

Figure 14.1. Two Windows Forms controls on a spreadsheet.

Sometimes you will have data that needs to be edited with a more effective user interface than Office provides. A good example of this is date input. Excel and Word provide a rich mechanism to display dates but do not provide an easy-to-use mechanism for entering dates other than basic text input. Windows Forms provides a DateTimePicker control that makes it easy for a user to enter a date. Combining the date entry interface provided by the DateTimePicker and the display capabilities of Excel or Word results in a more effective user interface.

You could integrate the DateTimePicker into your workbook, as shown in Figure 14.2. Here, we have added a DateTimePicker control for each cell containing a date. The DateTimePicker provides a combo-box drop-down list with a calendar that the user can use to pick a different date.

Figure 14.2. DateTimePicker controls on a spreadsheet.

The DateTimePicker may be better used in the Document Actions task pane than on the document surface, however. The first problem you will encounter with a solution such as the one shown in Figure 14.2 is what you will put in the spreadsheet for the values of the cells covered by the DateTimePicker controls. It would seem reasonable that the cell covered by a particular DateTimePicker control should contain the date value being represented by the control. This way, the date value for that cell can be used in formulas and can be found when the user searches the spreadsheet with Excel's Find command.

The second problem is that if you put the DateTimePicker on the document surface, the control does not automatically save its state into the Excel workbook when the document is saved. So, if in a particular session the user selects several dates and then saves the document, the next time the user opens the workbook, all the DateTimePickers will reset to today's date. You will lose the date the user picked in the last session unless you write code to synchronize the DateTimePicker with the cell value covered by it on startup of the Excel workbook and whenever the DateTimePicker or underlying cell value changes.

A third problem is keeping the DateTimePicker controls looking like the rest of the workbook formatting. If the user changes the font of the workbook, the controls embedded in the document will not change his font. Printing is also an issue because the control, replete with its drop-down combo widget, will be printed. In addition, the user will likely want to add and remove rows in the list of stocks, which means that you will have to add and remove DateTimePicker controls dynamically at runtime.

Although it is possible to work through these issues and achieve a reasonable solution, the actions pane may be an easier mechanism to use. The actions pane can show Windows Forms controls alongside the document in the Document Actions task pane rather than in the document. Whenever the user of your workbook has a date cell selected, for example, the Document Actions task pane can be displayed with the DateTimePicker in it to allow the user to pick a date, as shown in Figure 14.3. Chapter 15, "Working with the Actions Pane," discusses the actions pane.

Figure 14.3. Using the DateTimePicker control in the Document Actions task pane.

When to Use a Modal or Modeless Windows Forms Form

Another way to use Windows Forms in an Office application is to use a standard Windows Forms form shown as a dialog box. You could handle the BeforeDoubleClick event for the worksheet, for example, and if a cell containing a date is double-clicked, you could display a custom Windows Forms form, as shown in Figure 14.4.

Figure 14.4. Displaying a Windows Forms dialog box when the user double-clicks a cell.

This approach is also quite useful if you want to ensure that certain information is filled in before the user starts working with a document. You might want to display a wizard during the creation of a document that fills in certain portions of the document, for example.

A choice you must make when using Windows Forms as shown in Figure 14.4 is the modality of the form. A modal form must be interacted with and dismissed by clicking the OK, Cancel, or Close button before the user can get back to editing the document. A modeless Windows Forms can float above the document and still allow the user to interact with the document even though the form has not yet been closed. When using a modeless Windows Forms dialog box, note that an Office application can enter certain states where your modeless dialog box cannot be activated. If another modal dialog box is displayed, for example, users must dismiss the modal dialog box before they can interact with the modeless dialog box again. Cell-editing mode in Excel also affects modeless dialog boxes. If the user is editing a cell value in Excel, she cannot activate the modeless form until she leaves cell-editing mode.

Listing 14.1 shows a VSTO Excel customization that displays a simple modeless form. The modeless form has a button that, when clicked, shows a message box.

Listing 14.1. A VSTO Excel Customization That Displays a Modeless Form

Public Class Sheet1 Public WithEvents btn1 As Button Public form1 As Form Private Sub Sheet1_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup btn1 = New Button() form1 = New Form() form1.Controls.Add(btn1) form1.Show() AddHandler Globals.ThisWorkbook.BeforeClose, _ AddressOf ThisWorkbook_BeforeClose End Sub Private Sub btn1_Click(ByVal sender As Object, _ ByVal e As EventArgs) _ Handles btn1.Click MsgBox("You clicked me.") End Sub Private Sub ThisWorkbook_BeforeClose(ByRef Cancel As Boolean) form1.Close() End Sub End Class

Note that using the ActionsPane feature of VSTO is often an easier way to achieve a modeless result, because it provides all the benefits of a modeless form, with the addition of the ability to dock within the Office window space.