Of course, Excel offers a number of options for creating foolproof forms. Finally, save the worksheet and activate the Read-Only Recommended option. Turn off row and column headers, insert a nonprinting text box with your instructions for navigating in and saving the file, set the print range, and turn on worksheet protection. Then, select and unlock (unprotect) the cells in which the user will enter data. We set out to create a form that only required typing and tabbing.īegin by setting up your labels-the strings that describe the data you want the user to enter.
#HOW TO CREATE A DATA FORM IN EXCEL HOW TO#
I decided all this client needed was some training in how to make an Excel-based form as foolproof and as easy to use as possible. They set up an electronic form using Excel 97, but the sales reps kept overwriting formulas, changing text labels, and overwriting their original “clean” copies of the form.
They tried writing the information by hand on preprinted forms, but the penmanship was atrocious. All the client wanted was for her sales reps to fill out a single form, on a routine basis, for the sake of tracking internal accounting data. I hope you can put this tip to good use in your shops.Ī small business owner recently called with a problem. Here’s an Excel solution I’ve used to help many end users become more confident and competent in doing something as simple as filling out and printing an electronic form. This week, I’d like to do something about it. It’s just too easy to kvetch about how incompetent some end users can be. If you support users who have trouble entering data and printing an Excel sheet, use this eight-step approach to create a foolproof form.
#HOW TO CREATE A DATA FORM IN EXCEL CODE#
Private Sub CommandButton5_Click()Īs a last step, you need to input a few final pieces of code to create the drop-down values for the combo boxes (within the payment frames).Designing a foolproof data entry form in Excel In the exit button, enter the following code to close the user form. This can be done in the following manner: With Me In the clear button, you need to write the code to clear the existing values from the user form. We'll explain it thoroughly in the next section. If you're not sure what parts or any of the code means, don't worry. MsgBox "Please select the payment details below " 'Display a message box, in case the user selects the Yes radio button If optFemale.Value = True Then sht.Range( "g " & lastrow).Value = "Female " If optMale.Value = True Then sht.Range( "g " & lastrow).Value = "Male " Range( "r " & lastrow).Value = txtDept.Value Range( "q " & lastrow).Value = txtcourseduration.Value Range( "p " & lastrow).Value = txtenrollmentend.Value Range( "o " & lastrow).Value = txtenrollmentstart.Value Range( "n " & lastrow).Value = txtCourseID.Value Range( "m " & lastrow).Value = txtCourse.Value Range( "l " & lastrow).Value = txtNationality.Value Range( "k " & lastrow).Value = txtZip.Value Range( "j " & lastrow).Value = txtCountry.Value Range( "i " & lastrow).Value = txtCity.Value Range( "h " & lastrow).Value = txtPhone.Value Range( "g " & lastrow).Value = txtAddress.Value Range( "e " & lastrow).Value = txtDOB.Value Range( "d " & lastrow).Value = txtAge.Value Range( "c " & lastrow).Value = txtName.Value Range( "b " & lastrow).Value = txtStudentID.Value Range( "a " & lastrow).Value = txtApplicationNo.Value 'paste the values of each textbox into their respective sheet cells Lastrow = sht.Range( "a " & Rows.Count).End(xlUp).Row + 1 'calculate last populated row in both sheets Set sht = ThisWorkbook.Sheets( "Student Database ") 'link the text box fields with the underlying sheets to create a rolling database MsgBox "Only numeric values are accepted in Course ID", vbCritical If VBA.IsNumeric(Me.txtCourseID.Value) = False Then MsgBox "Only numeric values are accepted in Phone Number", vbCritical If VBA.IsNumeric(txtPhone.Value) = False Then MsgBox "Only numeric values are accepted in Age", vbCritical
If VBA.IsNumeric(txtAge.Value) = False Then MsgBox "Only numeric values are accepted in the Student ID", vbCritical If VBA.IsNumeric(txtStudentID.Value) = False Then MsgBox "Only numeric values are accepted in the Application Number", vbCritical If VBA.IsNumeric(txtApplicationNo.Value) = False Then 'Add validations to check if character values are being entered in numeric fields. ‘ declare the variables used throughout the codesĭim sht As Worksheet, sht1 As Worksheet, lastrow As Long
In the ensuing module, insert the following code: Private Sub CommandButton2_Click() Writing the Automated Form Code: Save Details Buttonĭouble-click on the Save Details button.
0 kommentar(er)
