How to Create User Forms With Excel 2003

Written by cheryl torrie
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Create User Forms With Excel 2003
Excel data entry forms make spreadsheets fun to use. (notebook image by Andrzej Solnica from Fotolia.com)

Creating user forms in Excel 2003 makes data entry a breeze. Forms that are utilised to collect and verify information from a user protect your worksheet from unnecessary changes and deletions that users could make if given access to the actual data list on the corresponding worksheet. Every form you create will request and gather different information but once you learn the Visual Basic for Applications (VBA) code for one you can build and customise any form in Excel 2003.

Skill level:
Easy

Other People Are Reading

Things you need

  • Microsoft Office Excel 2003

Show MoreHide

Instructions

  1. 1

    In the "Tools" menu, point to "Macro" and then click "Visual Basic Editor" or press "ALT" + "F11" to open the Visual Basic Editor. Open the "Main" menu, select "Insert" and click "User Form" to create a new UserForm object.

  2. 2

    Place a TextBox control and a Label control for each column in your worksheet onto the form by double-clicking the control or by dragging them from the Toolbox onto the form. Replace any TextBox and Label combination with a ComboBox control if you wish to present the user with a list of options to select from for the field rather than a text box.

  3. 3

    Assign the correct field name properties to the text and combo boxes by typing the corresponding database field names into the Name Property line in its corresponding property window. Change Caption Properties to a user-friendly display name at the same time.

  4. 4

    Double-click the "CommandButton" control tool four times to add the First, Previous, Next and Last buttons to your form. Type their names into the Caption Property line in their corresponding property windows.

  5. 5

    Drag a TextBox control onto the form in between the Previous and Next controls. Type "RowNumber" in its Name Property line. Type "2' on the Text Property line.

  6. 6

    Click or drag three additional CommandButton controls onto the form. Type "Save," "Cancel" and "Add" onto their corresponding Name Property lines. Set the Enabled Property to False on the Save and Cancel command buttons when you type the Name Properties.

  7. 7

    Arrange your controls and labels, then adjust the size of the user form with the click and drag method until you are sure that the form is easy to use and pleasing to a user.

  8. 8

    Add any additional text boxes and set their properties as needed for additional functionality and appeal. A form title box is recommended.

  9. 9

    Type this GetData routine into the VBA code window replacing your column names (field name properties) and data types with the sample CustomerId, CustomerName, City, State, Zip and DateAdded column names:

    "Private Sub GetData()

    Dim r As Long

    If IsNumeric(RowNumber.Text) Then

    r = CLng(RowNumber.Text)
    

    Else

    ClearData
    
    MsgBox "Illegal row number"
    
    Exit Sub
    

    End If

    If r > 1 And r <= LastRow Then

    CustomerId.Text = FormatNumber(Cells(r, 1), 0)
    
    CustomerName.Text = Cells(r, 2)
    
    City.Text = Cells(r, 3)
    
    State.Text = Cells(r, 4)
    
    Zip.Text = Cells(r, 5)
    
    DateAdded.Text = FormatDateTime(Cells(r, 6), vbShortDate)
    
    
    
    DisableSave
    

    ElseIf r = 1 Then

    ClearData
    

    Else

    ClearData
    
    MsgBox "Invalid row number"
    

    End If

    End Sub"

  10. 10

    Type this ClearData routine into the VBA code window. Replace your column names (field name properties) with the sample CustomerId, CustomerName, City, State, Zip and DateAdded column names:

    "Private Sub ClearData()

    CustomerId.Text = ""

    CustomerName.Text = ""

    City.Text = ""

    State.Text = "AK"

    Zip.Text = ""

    DateAdded.Text = ""

    End Sub"

  11. 11

    Type this constant LastRow command into the VBA code window:

    "Const LastRow = 20"

  12. 12

    Type this DisableSave routine into the VBA code window:

    "Private Sub DisableSave()

    CommandButton5.Enabled = False

    CommandButton6.Enabled = False

    End Sub"

  13. 13

    Type this routine named RowNumber_Change into the VBA code window:

    "Private Sub RowNumber_Change()

    GetData

    End Sub"

  14. 14

    Set the appropriate event command by typing this into the VBA code window:

    "RowNumber.Text = "2""

  15. 15

    Set the Previous and Next buttons codes by typing this into the VBA code window:

    "Private Sub CommandButton2_Click()

    Dim r As Long

    If IsNumeric(RowNumber.Text) Then

    r = CLng(RowNumber.Text)
    
    
    
    r = r ? 1
    
    If r &gt; 1 And r &lt;= LastRow Then
    
        RowNumber.Text = FormatNumber(r, 0)
    
    
    
    End If
    

    End If

    End Sub"

  16. 16

    Type this LastRow constant variable code into the VBA code window:

    "Private Sub UserForm_Initialize()

    GetData

    End Sub"

  17. 17

    Type this FindLastRow() routine into the VBA code window:

    "Private Function FindLastRow()

    Dim r As Long

    r = 2

    Do While r < 65536 And Len(Cells(r, 1).Text) > 0

    r = r + 1
    

    Loop

    FindLastRow = r

    End Function"

  18. 18

    Type these UserForm_Initialize events into the VBA code window:

    "LastRow = FindLastRow

    Private Sub CommandButton4_Click()

    LastRow = FindLastRow - 1

    RowNumber.Text = FormatNumber(LastRow, 0)

    End Sub"

  19. 19

    Type the PutData routine into the code window changing the sample column names and locations to match your worksheet:

    "Private Sub PutData()

    Dim r As Long

    If IsNumeric(RowNumber.Text) Then

        r = CLng(RowNumber.Text)
    

    Else

        MsgBox "Illegal row number"
    
        Exit Sub
    

    End If

    If r > 1 And r < LastRow Then

        Cells(r, 1) = CustomerId.Text
    
        Cells(r, 2) = CustomerName.Text
    
        Cells(r, 3) = City.Text
    
        Cells(r, 4) = State.Text
    
        Cells(r, 5) = Zip.Text
    
        Cells(r, 6) = DateAdded.Text
    
    
    
        DisableSave
    

    Else

        MsgBox "Invalid row number"
    

    End If

    End Sub"

  20. 20

    Type this Adding data routine into the code window:

    "Private Sub CommandButton7_Click()

    RowNumber.Text = FormatNumber(LastRow, 0)

    End Sub"

  21. 21

    Type this Validating Data routine into the code window:

    "Private Sub CustomerId_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    If KeyAscii < Asc("0") Or KeyAscii > Asc("9") Then

    KeyAscii = 0
    

    End If

    End Sub"

  22. 22

    Type this Exit event code into the VBA code window:

    "Private Sub DateAdded_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If Not IsDate(DateAdded.Text) Then

    DateAdded.BackColor = &amp;HFF&amp;
    
    MsgBox "Illegal date value"
    
    Cancel = True
    

    Else

    DateAdded.BackColor = &amp;H80000005
    

    End If

    End Sub"

  23. 23

    Type these combo box list details into the code window:

    "Private Sub AddStates()

    State.AddItem "AK"

    State.AddItem "AL"

    State.AddItem "AR"

    State.AddItem "AZ"

    End Sub"

  24. 24

    Type these Displaying the User Form commands into the VBA code window:

    "Public Sub ShowForm()

    UserForm1.Show vbModal

    End Sub"

  25. 25

    Review and text your form instructions and coding by selecting "Run" from the "Main Visual Basic for Applications Menu."

Tips and warnings

  • Do not include the beginning and ending quotation marks on the code strings. They are included to note what needs to be typed.
  • The VBA coding is set for a form with sample data. You need to adjust your field names and data types accordingly.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.