Section 5.16.Display Windows Forms


5.16. Display Windows Forms

Excel projects can use Windows forms to gather information and display results. Windows forms are a huge improvement over the forms you can display from Excel VBA.

5.16.1. How to do it

To create a Windows form in Visual Studio .NET for use from Excel, follow these steps:

  1. From the Project menu, choose Add Windows Form. Visual Studio .NET displays the Add New Item dialog box.

  2. Enter a name for the form and click OK. Visual Studio .NET creates a new Windows form class and displays the class in the Designer.

  3. Use the Designer to add controls to the form; then switch to the Code window. Unlike previous versions of Visual Basic, VB.NET describes the entire form in terms of code. The form and control properties are all maintained in the "Windows Form Designer generated code" region of the form's class.

  4. In order to enable the form to interact with Excel, add the following lines to the generated code (shown in bold ):

          Imports Excel = Microsoft.Office.Interop.Excel     Public Class SimpleForm         Inherits System.Windows.Forms.Form          Dim xlCode As OfficeCodeBehind     #Region " Windows Form Designer generated code "         Public Sub New(ByVal target As OfficeCodeBehind)             MyBase.New(  )             'This call is required by the Windows Form Designer.             InitializeComponent(  )             'Add any initialization after the InitializeComponent(  ) call             ' Get the OfficeCodeBehind class that created this form              ' (used to return responses to Excel).              xlCode = target         End Sub         ' Remainder of class omitted here...

  5. Within the form's event procedures, use the xlCode object created in Step 4 to interact with Excel. For example, the following code squares each of the values in the active worksheet when the user clicks the Square Values button:

         Private Sub cmdSquare_Click(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles cmdSquare.Click         ' If SquareCells succeeds, then close this form.         If SquareCells() Then Me.Close(  )     End Sub     Private Function SquareCells(  ) As Boolean         Try             ' For each cell in the active sheet's used range...             For Each cel As Excel.Range In _               xlCode.ThisWorkbook.ActiveSheet.UsedRange                 ' Square the value.                 cel.Value = cel.Value ^ 2             Next         Catch ex As System.Runtime.InteropServices.COMException             ' Something happened in Excel.             Debug.Fail(ex.Source & " " & Hex(ex.errorcode), ex.Message)             Return False         Catch ex As Exception             ' Something happened in .NET (display error while debugging)             Debug.Fail(ex.Source, ex.Message)             Return False         End Try         ' Success.         Return True     End Function

  6. Write code in the OfficeCodeBehind class to create the form and display it. For example, the following code creates a new form based on the SimpleForm class and displays it from Excel:

         ' In OfficeCodeBehind class.     Private Sub cmdSquare_Click(  ) Handles cmdForm.Click         ' Create a new form object.         Dim frm As New SimpleForm(Me)         frm.ShowDialog(  )     End Sub

5.16.2. How it works

The preceding procedure passes the OfficeCodeBehind class instance to the form's constructor in the code New SimpleForm(Me) . The form keeps that instance as the class-level xlCode variable defined in Step 4.

The .NET Framework provides two methods used to display forms: ShowDialog displays forms modally; the form stays on top and must be closed before the user returns to Excel. Show displays forms non-modally; the form may appear in front of or behind the Excel window, depending on which window has focus.

When working with Excel, you'll usually want to display Windows forms modally (using ShowDialog ). Exceptions to this rule might include cases where you want to display some output that you want to keep around, such as a floating toolbar or a Help window. In these cases, you can use the Show method combined with the TopMost property to keep the non-modal form displayed on top of Excel.

For example, the following code displays a new form based on the SimpleForm class non-modally, but keeps it on top of the other windows:

    Private Sub cmdSquare_Click(  ) Handles cmdForm.Click        ' Create a new form object.        Dim frm As New SimpleForm(Me)        ' Show the form non-modally but keep it on top.        frm.TopMost = True         frm.Show(  )    End Sub



    Excel 2003 Programming. A Developer's Notebook
    Excel 2003 Programming: A Developers Notebook (Developers Notebook)
    ISBN: 0596007671
    EAN: 2147483647
    Year: 2004
    Pages: 133
    Authors: Jeff Webb

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net