Creating a Custom Task Pane and Data Input Form Using NET

In this example, we'll look at a .NET project that creates a custom task pane and a simulated Excel UserForm. A task pane is a window that anchors itself to the right of an Office application and contains commands to perform various functions. A common Office task pane is the Getting Started task pane, shown in Figure 9-21.

Create Custom Input Forms Powershell
Figure 9-21. Getting Started task pane in Excel 2003

Our example task pane will contain commands used by a human resources department to enter new hire information and send that information to other groups for processing.

Creating the HR Task Pane Add-In

To begin, we'll need to add a couple of new items to our add-in project: a user control that will contain the task pane and a Windows form to act as our Excel 2007 UserForm.

1. Open Visual Studio 2005.

2. From the start page, create a new Microsoft Excel Add-in project.

3. Name it UserFormAddIn.

4. Add a new user control to the project by selecting Project > Add User Control.

5. In the Add New Item dialog box, name the user control HRTaskPane.vb.

6. Add a new Windows form to the project by selecting Project > Add Windows Form.

7. In the Add New Item dialog box, name the Windows form NewEmpForm.vb.

The Custom Task Pane Our custom task pane will contain two commands. The first will open our Windows form to collect new employee information. The second will send that information to other departments who might need it.

1. Open the User Control Designer by double-clicking HRTaskPane.vb in the Solution Explorer.

2. Click the Toolbox (on the left side of the Visual Studio window) to unhide it (if it's not already displayed).

3. Click the pin (Auto Hide) button to leave the Toolbox displayed.

4. Add two Button controls from the Common Controls section (Figure 9-22) to the user control by dragging them onto the Designer.

5. In the Properties pane, change the Text properties of the two buttons to New Employee and E-mail Info, respectively, as shown in Figure 9-22.

6. In the Properties pane, name the New Employee button btnLaunch.

7. In the Properties pane, name the E-mail Info button btnEmail.

Figure 9-22. Completed HR task pane with Auto Hide command displayed

8. Double-click the New Employee button to open its code stub.

9. Add the following code to btnLaunch_Click:

Dim oForm As New NewEmpForm oForm.ShowDialog()

This code creates a new instance of our NewEmpForm and opens it in dialog mode (so it remains attached to the Excel window).

10. Click back on the HRTaskPane.vb [Design] tab, and then double-click the E-mail Info button.

11. Add the following code to btnEmail_Click: Dim rng As Excel.Range rng = Globals.ThisAddIn.Application.Range("A6") 'code to handle e-mail here

MsgBox("Sending new hire information for" & rng.Text & " to Systems Group")

This is basically a dummy function to simply show that we can process the data from the task pane and place it anywhere else we'd like.

12. Close the HRTaskPane design and code windows. If prompted to save changes, choose Yes.

Showing the Custom Task Pane Now that we've got our custom task pane set up, we need add code to show it when our add-in starts up. The ThisAddIn.vb code file that Visual Studio 2005 created for us came complete with two code stubs for handling add-in startup and shutdown.

Note The startup method contains one line of code generated by VSTO. This tells the add-in what application it's attaching itself to.

1. Display the ThisAddIn.vb code window by clicking its tab in the Visual Studio display area (if it's not there, double-click it in the Solution Explorer).

2. Place the insertion point in the blank line below the end of the VSTO-generated code.

3. Add the following code to display the HRTaskPane control: Dim MyTaskPane As New HRTaskPane

Dim MyCustomTaskPane As Microsoft.Office.Tools.CustomTaskPane = Me.CustomTaskPanes.Add(MyTaskPane, "HR Tasks") MyCustomTaskPane.Visible = True

This code adds our HRTaskPane control to the add-in's CustomTaskPanes collection. In the call to the CustomTaskPanes.Add method, the second argument is the text that will display in the title bar of the task pane when it is displayed. Finally, we make the task pane visible.

Creating an Excel UserForm Using a Windows Form So far, we've created a task pane with two commands and added code to our add-in project to display the custom task pane. The last things for us to do are add controls to our Windows form to collect data and add commands to put the data on the active worksheet.

1. Open NewEmpForm.vb in Design view by double-clicking it in the Solution Explorer.

2. Add six labels, six text boxes, and two Button controls from the Common Controls Toolbox, and lay them out as shown in Figure 9-23.

Excel Forms
Figure 9-23. Completed employee data entry UserForm 3. Name the text boxes and buttons per Table 9-1.
Table 9-1. New Employee Form Control Properties

Item

Property

Value

Form

Text

New Employee Form

TextBoxl

Name

txtFName

TextBox2

Name

txtMidInit

TextBox3

Name

txtLName

TextBox4

Name

txtDOH

TextBox5

Name

txtTitle

TextBox6

Name

txtReportsTo

Labell

Text

First Name

Label2

Text

Mid Init

Label3

Text

Last Name

Label4

Text

Date of Hire

Label5

Text

Job Title

Label6

Text

Reports To

Buttonl

Name

btnSave

Buttonl

Text

Save

Button2

Text

btnCancel

Button2

Text

Cancel

Now that we have our controls set, let's add code to create the display form in Excel 2007 and place the data from our Windows form onto the worksheet.

4. Display the Save button code stub by double-clicking the Save button.

Our Save button will do three things:

• Set up the worksheet by adding headings and adjusting column widths

• Put the data from the data entry form on the worksheet

• Close the data entry form

5. Add the following code to the btnSave_Click event:

FormatForm()

PlaceData()

Close()

As you can see, each command maps to one of the three functions that the Save command will perform. The Close method is a built-in method of the Windows form object. Let's add the code for the FormatForm and PlaceData methods.

6. On the NewEmpForm.vb code module, add a new subroutine and name it FormatForm.

7. Add the following code to the FormatForm subroutine:

DoHeadings() Dim rng As Excel.Range With Globals.ThisAddIn.Application rng = .Range("A5") rng.Value = "First Name" rng.Font.Bold = True rng.ColumnWidth = 15 rng = .Range("B5") rng.Value = "Mid Init" rng.Font.Bold = True rng.ColumnWidth = 15 rng = .Range("C5") rng.Value = "Last Name" rng.Font.Bold = True rng.ColumnWidth = 15 rng = .Range("A8") rng.Value = "Date of Hire" rng.Font.Bold = True rng = .Range("B8") rng.Value = "Job Title" rng.Font.Bold = True rng = .Range("C8") rng.Value = "Reports To" rng.Font.Bold = True End With rng = Nothing

The DoHeadings method will put the title and subtitle on the worksheet. The repeated reference to the rng variable sets the active cell, formats it, and places any text labels in the cell.

Note We have a reference to the Visual Basic Globals module in our With block. We saw the same reference earlier in our btnEmail_Click event on our custom task pane object. In order to access objects in an Excel workbook (or any Office application object), we must go through the Globals module. This module supports the runtime library members that contain information about the runtime currently being used.

8. Add another subprocedure and name it DoHeadings.

9. Add the following code:

Dim rng As Excel.Range With Globals.ThisAddIn.Application rng = .Range("A1") rng.Value = "HR Data Entry System" rng.Font.Bold = True rng.Font.Size = 16 rng = .Range("A2")

rng.Value = "New Employee Information" rng.Font.Italic = True rng.Font.Size = 14 End With rng = Nothing

There's nothing new here. This code works exactly like the FormatForm subroutine.

Next, let's add the code to put the data on the worksheet.

10. Add a new subroutine, and name it PlaceData.

11. Add the following code:

Dim rng As Excel.Range With Globals.ThisAddIn.Application rng = .Range("A6") rng.Value = Me.txtFName.Text rng = .Range("B6") rng.Value = Me.txtMidInit.Text rng = .Range("C6") rng.Value = Me.txtLName.Text rng = .Range("A9") rng.Value = Me.txtDOH.Text rng = .Range("B9") rng.Value = Me.txtTitle.Text rng = .Range("C9") rng.Value = Me.txtReportsTo.Text End With

Again, we're not doing anything new here—we're just breaking the functionality up into smaller pieces.

The last thing to do is to code the Cancel button.

12. Select btnCancel from the Class Name drop-down list on the code designer.

13. Select its click event from the Method Name list.

14. In the btnCancel_Click event code stub, add the following line of code: Close()

That is all the code we need to write. Now let's run the application and see how it works.

Running the Add-In Now that the user control, the Excel Add-in, and the Windows form have all been coded, let's run the project and take a look at what we've done.

1. Run the project by selecting Debug > Start Debugging or pressing the F5 key.

Excel 2007 opens with a blank workbook displayed and our custom task pane anchored to the right of the workbook, as shown in Figure 9-24.

¿b pro

JN jj ;'J i = Bookl - Microsoft Excel _ B X

** | Home

Insert Page Layout Formulas Data Review View Developer ® & 5C

jt

Caftbrr '11 -

= = =¡1=

% Number

Styles

SI' ¡3§* a*

J ^

B I H '1 A* Al

\m » m\s~

j

«MI^A-f

1« wh

Clipboa... Q

Font äg!

Alignment Sä

Editing

Al - & f,

A

B

o : E : i

t HR Tasks ▼ X

1

2

3

[ New Employee ]

4

5

E-mail Info

6

7

S

9

10

11

12

> * i

1

n ^ t H Sheetl Sheet2 Shesl'H 1 111

Ready

mmm 100% -t-n

Figure 9-24. Excel 2007 workbook with custom task pane

2. Click the New Employee button on the HR task pane to display the data entry form.

3. Enter data on the New Employee form. Sample data is shown in Figure 9-25.

ËH New Employee Form T] [□][><]

First Name

Jim

Mid Irût

|j

Last Name

De Marco

Date of Hire

|l/24/2M)

Job Title

Developer

Reports To

VP of Technologyl

Save

I Cancel |

1 1

Figure 9-25. New Employee form with sample data

4. Click the Save button to place the data on the worksheet and format the sheet, as shown in Figure 9-26.

(p.-.

* Bookl - Microsoft Excel

' \ Home

Insert

Page Layout Formutas Data Review

View Developer | ® - a x

Paste

?

Cafibri

16 -

m = m i=

J Number

Style s

o-

M

A" A1

|* * ■ w

LH

W

Clipboa... ^

Font

Alignment **

Editing

Al

HR Data Entry

Systerr

El

s

c

i

t HR Tasks ▼ X

1

HR Data Ent

y System

2

New Employee Information

[ New Employee j

3

4

E-mail Info

5

First Name

Mid Init

Last Name

[

S

Jim

J

DeMarco

7

1

S

Date of Hire

Job Title

Reports To

9

1/24/2000

Developer

VP of Technology

10

11

12

heetl

1

Ready 3

Sheet2 ■ Sheej'l-C

in, 1 laa iiiii iooîî

Figure 9-26. Data and formatting applied to active worksheet

5. Close the workbook without saving.

6. In Visual Studio 2005, save the project file.

Caution As with the previous example, the add-in will remain loaded for all Excel workbooks until you manually remove it.

+2 0

Post a comment