How to Create Forms Using Excel

Create a form in Excel, utilizing controls grouped in a frame, which will produce simple text fields where the user can enter her information. Create three command buttons on the form that will have an effect on the information in the form when the user performs a left-click with her mouse. It's simple to create a form that will list employees or managers, and check their status.

Instructions

1. Open a workbook in Excel. If you know which workbook you want your form to belong to, then now is the time to open it. Use the File menu to access the workbook.

2. Click on the "Tools" menu, select "Macro" and click on "Visual Basic Editor."

3. Click "Insert," and then select "UserForm."

4. Drag a command button onto the form. You need at least three of these for this example.

5. Put names on your command buttons and labels. Click "View" and "Properties Window." Click on one of the command buttons that you placed on the form.

6. Name the command button that will read "OK" by clicking on the name setting in the Properties window and typing "cmdOK". Click on the "Caption" setting in the Properties window and type "OK".

7. Click on another command button. Name this one "cmdCancel," and set the caption to read "Cancel" in the Properties window.

8. Click on the third command button, name it "cmdClearForm" and change the caption setting to read "Clear Form."

9. Hit the "F7" function key on your keyboard to bring up the code window.

10. Click on the drop-down lists at the top of the code window. Click on the top-left list to open the subprocedure named "User Form" and click on the other drop-down list to select "Initialize."

11. Delete the subprocedure listed above that reads: UserForm_Click() procedure.

12. Type the following code into the code window (do not type over the blue text; just add the black text):
    Private Sub UserForm_Initialize()
    txtName.Value = ""
    txtPhone.Value = ""
    With cboDepartment
    .AddItem "Employees"
    .AddItem "Managers"
    End With

    YourCourse.Value = ""
    optIntroduction = True
    chkWork = False
    chkVacation = False
    txtName.SetFocus
    End Sub

13. Enter your code into the Cancel button. Double-click the Cancel button to open the code window. Type your code so that the code window reads: Private Sub cmdCancel_Click()
    Unload Me
    End Sub

14. Open the code window for the Clear Form button. Remember to double-click the "Clear Form" button.

15. Type your code into the code window so that it reads:
    Private Sub cmdClearForm_Click()
    Call UserForm_Initialize
    End Sub

16. Double-click the "OK" button to add the following code in the code window:

    Private Sub cmdOK_Click()
    ActiveWorkbook.Sheets("YourWork").Activate
    Range("A1").Select
    Do
    If IsEmpty(ActiveCell) = FalseThen
    ActiveCell.Offset(1, 0).Select
    End If
    Loop Until IsEmpty(ActiveCell) = True
    ActiveCell.Value = txtName.Value
    ActiveCell.Offset(0, 1) = txtPhone.Value
    ActiveCell.Offset(0, 2) = cboDepartment.Value
    ActiveCell.Offset(0, 3) = cboCourse.Value
    If optIntroduction = True Then
    ActiveCell.Offset(0, 4).Value = "Intro"
    ElseIf optIntermediate = True Then
    ActiveCell.Offset(0, 4).Value = "Intermed"
    Else
    ActiveCell.Offset(0, 4).Value = "Adv"
    End If
    If chkLunch = True Then
    ActiveCell.Offset(0, 5).Value = "Yes"
    Else
    ActiveCell.Offset(0, 5).Value = "No"
    End If
    If chkWork = True Then
    ActiveCell.Offset(0, 6).Value = "Yes"
    Else
    If chkVacation = False Then
    ActiveCell.Offset(0, 6).Value = ""
    Else
    ActiveCell.Offset(0, 6).Value = "No"
    End If
    End If
    Range("A1").Select
    End Sub

References

• Excel Tips: Create Forms in Excel

Resources

• Read this Article in Spanish