Share via


Walkthrough: Collecting Data Using a Windows Form

This walkthrough demonstrates how to open a Windows Form from a document-level customization for Microsoft Office Excel, collect information from the user, and write that information into a worksheet cell.

Applies to: The information in this topic applies to document-level projects and application-level projects for Microsoft Office 2010 and the 2007 Microsoft Office system. For more information, see Features Available by Office Application and Project Type.

Although this walkthrough uses a document-level project for Excel specifically, the concepts demonstrated by the walkthrough are applicable to other Office projects.

Prerequisites

You need the following components to complete this walkthrough:

-

An edition of Visual Studio 2010 that includes the Microsoft Office developer tools. For more information, see [Configuring a Computer to Develop Office Solutions](bb398242\(v=vs.100\).md).
  • Microsoft Office Excel 2007 or Excel 2010.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Working with Settings.

Creating a New Project

The first step is to create an Excel Workbook project.

To create a new project

  • Create an Excel Workbook project with the name WinFormInput, and select Create a new document in the wizard. For more information, see How to: Create Office Projects in Visual Studio.

    Visual Studio opens the new Excel workbook in the designer and adds the WinFormInput project to Solution Explorer.

Adding a NamedRange Control to the Worksheet

To add a named range to Sheet1

  1. Select cell A1 on Sheet1.

  2. In the Name box, type formInput.

    The Name box is located to the left of the formula bar, just above column A of the worksheet.

  3. Press ENTER.

    A NamedRange control is added to cell A1. There is no visible indication on the worksheet, but formInput appears in the Name box (just above the worksheet on the left side) and in the Properties window when cell A1 is selected.

Adding a Windows Form to the Project

Create a Windows Form to prompt the user for information.

To add a Windows Form

  1. Select the project WinFormInput in Solution Explorer.

  2. On the Project menu, click Add Windows Form.

  3. Name the form GetInputString.vb or GetInputString.cs, and then click Add.

    The new form opens in the designer.

  4. Add a TextBox and a Button to the form.

  5. Select the button, find the property Text in the Properties window, and change the text to OK.

Next, add code to ThisWorkbook.vb or ThisWorkbook.cs to collect the user's information.

Displaying the Windows Form and Collecting Information

Create an instance of the GetInputString Windows Form and display it, and then write the user's information into a cell in the worksheet.

To display the form and collect information

  1. Right-click ThisWorkbook.vb or ThisWorkbook.cs in Solution Explorer, and then click View Code.

  2. In the Open event handler of ThisWorkbook, add the following code to declare a variable for the form GetInputString and then show the form.

    Note

    In C#, you must add an event handler as shown in the Startup event below. For information about creating event handlers, see How to: Create Event Handlers in Office Projects.

    Private Sub ThisWorkbook_Open() Handles Me.Open
        Dim inputForm As New GetInputString()
        inputForm.Show()
    End Sub
    
    private void ThisWorkbook_Startup(object sender, System.EventArgs e)
    {
        this.Open += new 
            Microsoft.Office.Interop.Excel.WorkbookEvents_OpenEventHandler(ThisWorkbook_Open);
    }
    
    private void ThisWorkbook_Open()
    {
        GetInputString inputForm = new GetInputString();
        inputForm.Show();
    }
    
  3. Create a method called WriteStringToCell that writes text to a named range. This method is called from the form, and the user's input is passed to the NamedRange control, formInput, on cell A1.

    Public Sub WriteStringToCell(ByVal formData As String)
        Globals.Sheet1.formInput.Value2 = formData
    End Sub
    
    public void WriteStringToCell(string formData)
    {
        Globals.Sheet1.formInput.Value2 = formData;
    }
    

Next, add code to the form to handle the button's click event.

Sending Information to the Worksheet

To send information to the worksheet

  1. Right-click GetInputString in Solution Explorer, and then click View Designer.

  2. Double-click the button to open the code file with the button's Click event handler added.

  3. Add code to the event handler to take the input from the text box, send it to the function WriteStringToCell, and then close the form.

    Globals.ThisWorkbook.WriteStringToCell(Me.TextBox1.Text)
    Me.Dispose()
    
    Globals.ThisWorkbook.WriteStringToCell(this.textBox1.Text);
    this.Dispose();
    

Testing

You can now run the project. The Windows Form appears, and your input appears in the worksheet.

To test your workbook

  1. Press F5 to run your project.

  2. Confirm that the Windows Form appears.

  3. Type Hello World in the text box, and then click OK.

  4. Confirm that Hello World appears in cell A1 of the worksheet.

Next Steps

This walkthrough shows the basics of showing a Windows Form and passing data to a worksheet. Other tasks you may want to perform include:

See Also

Tasks

How to: Interact with Windows Forms

Concepts

Writing Code in Office Solutions

Walkthroughs Using Word

Walkthroughs Using Excel

Globalization and Localization of Office Solutions

Other Resources

Developing Office Solutions

Programming Application-Level Add-Ins

Programming Document-Level Customizations