How to Create User Forms With Excel 2003

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. 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. 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. 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. 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. 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. 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. Add any additional text boxes and set their properties as needed for additional functionality and appeal. A form title box is recommended.
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 LongIf IsNumeric(RowNumber.Text) Thenr = CLng(RowNumber.Text)ElseClearDataMsgBox 'Illegal row number'Exit SubEnd IfIf r > 1 And r
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. Type this constant LastRow command into the VBA code window:'Const LastRow = 20'
12. Type this DisableSave routine into the VBA code window:'Private Sub DisableSave()CommandButton5.Enabled = FalseCommandButton6.Enabled = FalseEnd Sub'
13. Type this routine named RowNumber_Change into the VBA code window:'Private Sub RowNumber_Change()GetDataEnd Sub'
14. Set the appropriate event command by typing this into the VBA code window:'RowNumber.Text = '2''
15. Set the Previous and Next buttons codes by typing this into the VBA code window:'Private Sub CommandButton2_Click()Dim r As LongIf IsNumeric(RowNumber.Text) Thenr = CLng(RowNumber.Text)r = r ? 1If r > 1 And r
16. Type this LastRow constant variable code into the VBA code window:'Private Sub UserForm_Initialize()GetDataEnd Sub'
17. Type this FindLastRow() routine into the VBA code window:'Private Function FindLastRow()Dim r As Longr = 2Do While r
0r = r 1LoopFindLastRow = rEnd Function'
18. Type these UserForm_Initialize events into the VBA code window:'LastRow = FindLastRowPrivate Sub CommandButton4_Click()LastRow = FindLastRow - 1RowNumber.Text = FormatNumber(LastRow, 0)End Sub'
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 LongIf IsNumeric(RowNumber.Text) Thenr = CLng(RowNumber.Text)ElseMsgBox 'Illegal row number'Exit SubEnd IfIf r > 1 And r
20. Type this Adding data routine into the code window:'Private Sub CommandButton7_Click()RowNumber.Text = FormatNumber(LastRow, 0)End Sub'
21. Type this Validating Data routine into the code window:'Private Sub CustomerId_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)If KeyAscii
Asc('9') ThenKeyAscii = 0End IfEnd Sub'
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) ThenDateAdded.BackColor = HFFMsgBox 'Illegal date value'Cancel = TrueElseDateAdded.BackColor = H80000005End IfEnd Sub'
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. Type these Displaying the User Form commands into the VBA code window:'Public Sub ShowForm()UserForm1.Show vbModalEnd Sub'
25. Review and text your form instructions and coding by selecting 'Run' from the 'Main Visual Basic for Applications Menu.'