Introduction

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 that 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. Visual Studio Tools for Office (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:

1. You can put a Windows Forms control on the document or spreadsheet surface.
2. You can display a custom Windows Forms form as a modal or modeless dialog.
3. 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 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 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. For example, many stockbrokers use Excel as their primary input and display mechanism when trading. 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 that shows a calendar that the user can use to pick a different date.

Figure 14-2. DateTimePicker controls on a spreadsheet.

However, the DateTimePicker may be better used in the Document Actions task pane than on the document surface. The first problem you will encounter with a solution such as the one shown in Figure 14-2 is what will you 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 change.

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 their 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 to the list of stocks, which means that you will have to dynamically add and remove DateTimePicker controls 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 along side the document in the Document Actions task pane rather than in the document. For example, whenever the user of your workbook has a date cell selected, 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 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. For example, you could handle the BeforeDoubleClick event for the worksheet 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 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. For example, you might want to display a wizard during the creation of a document that fills in certain portions of the document.

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, note that there are certain states an Office application can enter where your modeless dialog cannot be activated. For example, if another modal dialog is displayed, users must dismiss the modal dialog before they can interact with the modeless dialog again. Cell editing mode in Excel also affects modeless dialogs. If the user is editing a cell value in Excel, the user cannot activate the modeless form until the user 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 the ID of the thread that the button handler is invoked on. The ID of the main Office UI thread is also shown in the Startup event.

Listing 14-1. A VSTO Excel Customization That Displays a Modeless Form

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using Microsoft.VisualStudio.OfficeTools.Interop.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;

namespace ExcelWorkbook1
{
 public partial class Sheet1
 {
 public Button btn1;
 public Form form1;

 private void Sheet1_Startup(object sender, EventArgs e)
 {
 MessageBox.Show(System.Threading.Thread.ManagedThreadID);
 btn1 = new Button();
 btn1.Click += new EventHandler(btn1_Click);

 form1 = new Form();
 form1.Controls.Add(btn1);
 form1.Show();

 Globals.ThisWorkbook.BeforeClose += 
 new Excel.WorkbookEvents_BeforeCloseEventHandler(
 ThisWorkbook_BeforeClose);
 }

 void btn1_Click(object sender, EventArgs e)
 {
 MessageBox.Show(System.Threading.Thread.ManagedThreadID);
 }

 void ThisWorkbook_BeforeClose(ref bool Cancel)
 {
 form1.Close();
 }

 #region VSTO Designer generated code
 private void InternalStartup()
 {
 this.Startup += new System.EventHandler(this.Sheet1_Startup);
 }
 #endregion
 }
}

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.