XL: How to Use a Custom Dialog Box for Data Entry

Last reviewed: January 9, 1998
Article ID: Q141289
The information in this article applies to:
  • Microsoft Excel for Windows, version 5.0, 5.0c
  • Microsoft Excel for Windows 95, version 7.0
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a
  • Microsoft Excel for Windows NT, version 5.0

SUMMARY

The following Microsoft Visual Basic for Applications procedure uses edit boxes on a custom dialog sheet to receive your data and place that data on a worksheet.

MORE INFORMATION

After you have entered the data into the edit boxes, press ENTER or click the OK button on the custom dialog box to enter the data on the worksheet, reinitialize the dialog box, and display it for the next record. Click Cancel to halt the macro.

Sample Visual Basic Procedure

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

To create the procedure, follow these steps:

  1. Open a new workbook and rename Sheet1 to Data.

    This sheet will hold the entered data.

  2. On the Data sheet, type the following text:

          A1: First Name   B1: Last Name   C1: Department
    
    

  3. Select cells A1:C1, and click Cells on the Format menu. Click the Format tab and, under Font Style, click Bold. Click the Border tab, and click the Left, Right, Top, and Bottom boxes to format these cells with borders on all four sides of the cell. Adjust column widths as necessary.

  4. To insert a dialog sheet, click Macro on the Insert menu, and then click Dialog. Rename the sheet to Dialog.

  5. Use the Forms toolbar to place three edit boxes on the dialog box. Then, place a label above each edit box. The first label should be First Name, the second should be Last Name, and the third label should be Department.

  6. To Assign a name to each edit box, select the edit box, click the Name Box on the far left of the Formula Bar, type the appropriate name, and press ENTER (you must press ENTER or the name will not be defined). Name the edit boxes as follows:

        - Define First Name as "fname" (without the quotation marks)
        - Define Last Name as "lname" (without the quotation marks)
        - Define Department as "dept" (without the quotation marks)
    

  7. Insert a module sheet by clicking Macro on the Insert menu and then clicking Module.

  8. Type the following Visual Basic for Applications macros on the Module sheet. Lines preceded by the apostrophe (') are comments.

          ' Dimension the variables to be used to control the procedures.
          Dim StopFlag As Integer 'Used to control the loop
    
          Dim RowNum As Single    'Used to determine data input line
    
          ' Controlling procedure that sets up initial values of variables and
          ' holds the loop that calls the actual working procedures.
    
          Sub Main_Procedure()
    
             ' Determine the current number of rows on the Data worksheet.
             RowNum = ThisWorkbook.Worksheets("data").Range("a1" _
                ).CurrentRegion.Rows.Count
    
             ' Set the flag used to control the loop to it's initial value.
             StopFlag = 0
    
             ' This loop blanks the edit boxes on the dialog sheet, shows the
             ' dialog box, and enters the data to the worksheet.
             Do Until StopFlag = 1
                Initialize_Dialog
                DialogSheets("Dialog").Show
                Enter_Data_on_Worksheet
             Loop
          End Sub
    
          ' This procedure sets the edit boxes on the dialog sheet as empty.
    
          Sub Initialize_Dialog()
             With DialogSheets("Dialog")
                .EditBoxes("fname").Text = ""
                .EditBoxes("lname").Text = ""
                .EditBoxes("dept").Text = ""
             End With
          End Sub
    
          ' Place data in worksheet on row indicated by RowNum.
    
          Sub Enter_Data_on_Worksheet()
             With Worksheets("Data")
                .Range("a1").Offset(RowNum, 0) = _
                   DialogSheets("Dialog").EditBoxes("fname").Text
                .Range("a1").Offset(RowNum, 1) = _
                   DialogSheets("Dialog").EditBoxes("lname").Text
                .Range("a1").Offset(RowNum, 2) = _
                   DialogSheets("Dialog").EditBoxes("dept").Text
             End With
             RowNum = RowNum + 1     ' Shift indicator to next line.
          End Sub
    
          Sub Stop_Loop()
             StopFlag = 1     ' Set flag to halt loop.
          End Sub
    
    

  9. On the Dialog sheet, use the right mouse button to click the Cancel button and click Assign Macro on the shortcut menu. Choose the Stop_Loop macro and click OK.

To run this macro, click Macro on the Tools menu, select the Main Procedure macro, and click Run.


Additional query words: 5.00 5.00a 5.00c 7.00
Keywords : kbcode kbprg PgmHowto
Version : WINDOWS:5.0,5.0c,7.0; MACINTOSH:5.0,5.0a
Platform : MACINTOSH WINDOWS
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: January 9, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.