How to Create Forms Using Excel

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 WithYourCourse.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