If you’re building an Excel workbook, in which users with basic Excel skills will enter data, would you create a worksheet data entry form? In the screen shot below, you can see an example.
Or, do you prefer to build an Excel UserForm? In the screen shot below, you can see a simple UserForm.
Worksheet Data Entry
With the worksheet method, you can
- hide the data sheets, and protect the data entry sheets, so users can only enter data in the unlocked cells.
- add a few navigation and function buttons, to help users with basic Excel skills.
An advantage is that you’re using built-in Excel features, like data validation and formulas, so you can reduce the development time.
The UserForm method takes longer to develop, because you’re adding another layer to the project. Advantages to this method include:
- combo boxes, which can be formatted, and have autocomplete (unlike data validation drop downs)
- tab order control, which isn’t available on the worksheet, where pressing the Tab key simply takes you to the next unlocked cell.
Which Would You Pick?
Both methods work well, and can be customized to be user-friendly and fool-resistant (nothing in Excel is fool-proof!) Programming would be required in both versions, to help with navigation, and to move data to the storage worksheets.
- The worksheet method is quicker and easier to create and maintain, and a project might take 4-5 hours to complete.
- The UserForm method is more sophisticated, and takes longer to build and maintain. The UserForm version of the same project might take 8-10 hours.
Which method would you use?
Excel VBA Training
If you want to improve your Excel VBA skills, Chandoo’s Excel VBA online training program is a valuable investment. The course runs for 12 weeks, and you’ll have 6 months of access to the lessons, with code examples and sample files. There are online discussion areas too, where you can ask questions, and get help if you’re stuck on one of the lessons.
The course is highly rated by Chandoo’s students, and it comes with a 30 day money back guarantee, so you can register for the course with no risk.