Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

Walkthrough: Collecting Data Using a Windows Form

NoteNote

Some code examples in this topic use the this or Me keyword or the Globals class in a way that is specific to document-level customizations, or they rely on features of document-level customizations such as host controls. These examples can be compiled only if you have the required applications installed. For more information, see Features Available by Product Combination.

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

To complete this walkthrough, you will need:

  • Microsoft Visual Studio 2005 Tools for the Microsoft Office System.

  • Excel

NoteNote

The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.

In this step, you will create an Excel Application project.

To create a new project

  • Create an Excel Workbook project with the name WinFormInput. Make sure that Create a new document is selected. For more information, see How to: Create Visual Studio Tools for Office Projects.

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

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.

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.

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 select 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:

    NoteNote

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

    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 will be passed to the NamedRange control, formInput, on cell A1.

    public void WriteStringToCell(string formData)
    {
        Globals.Sheet1.formInput.Value2 = formData;
    }
    
    

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

To send information to the worksheet

  1. Right-click GetInputString in Solution Explorer, and select 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(this.textBox1.Text);
    this.Dispose();
    
    

You can now test your workbook to make sure that the Windows Form appears, and that 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 click OK.

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

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

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

Show:
© 2015 Microsoft