Beyond Excel: VBA and Database Manipulation

Sep 24 2014   8:06AM GMT

Excel Data Entry Protection Solution

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

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.

Solution: Cursor Control Add-in
YouTube

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.

https://dl.dropboxusercontent.com/u/13737137/Projects/Input/Cursor.pdf

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

 

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: