Excel’s grid is perfect for some types of data entry, but in its startup mode, Excel is too wide open. For data entry we need to restrict entries to specific cells and specific values. Excel’s answer is Worksheet Protection and Data Validation. For me, Excel’s Worksheet Protection falls short.
Problem #1: Restricting Entries to Specific Cells.
Besides being cumbersome to setup, Excel’s Worksheet Protection prevents inserting or adding table rows. Excel’s grid is perfect for repeating rows of data which are required by Journal Entries, Time Sheets, Rate Tables and more. Preventing table row adds cripples Excel’s ability to work with these data sets.
Problem #2: Disabling Features
Excel’s Worksheet Protection also disables some of Excel’s wonderful features such as table sorting and filtering.
Problem #3: Data Validation and Conditional Formatting
Excel’s Worksheet Protection does nothing to prevent copy/paste operations from wiping out Data Validation and Conditional Formatting. Both of these features are absolutely critical to data entry. Good data entry apps MUST restrict entries to valid data and MUST convey errors which is normally done by text and highlighting cells in red or yellow.
This free add-in was designed to address these problems.
Easy Setup – Just format data entry cells with the INPUT style. INPUT, BAD, GOOD and NEUTRAL are all considered input styles. All other styles, including NORMAL are protected.
Table Rows – Cursor Control add’s rows to the bottom of tables when the user selects the last row. It also allows inserting table rows.
Sorting and Filtering – Cursor Control preserves almost all of Excel’s features such as sorting and filtering
Data Validation – Cursor Control prevents copy/paste operations (or autofill) from wiping out data validation.
Cursor Control is available as a free Excel add-in: Cusor.xlam. As an add-in it can be applied to any worksheet without VBA. If you prefer tighter control, Cusor.xlam’s source code is unprotected permitting its code to be copied directly into other projects. This PDF explains how to: download Cursor Control;; add it as an add-in; use it; and directly incorporate its code it into any project.
Download the PDF at: https://dl.dropboxusercontent.com/u/13737137/Projects/Input/Cursor.pdf
Discuss this post or other BXL topics at: facebook.com/BeyondExcel