Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba
Updating databases demands discipline. Excel is about freedom. It’s what your users love about it. Even so, updating databases demands discipline and striking the right balance between freedom and discipline is key to making Excel a great tool for users and DBAs.
We’ve just invested most of this blog discussing ways to insure users don’t get too free with their data. We not only made sure users didn’t enter bad data, but we went the extra mile and provided tools for helping them find what they need, like pop-up windows to search databases for the right User ID, Customer Code, General Ledger Account Number, Inventory Item, etc. We enforced restrictions and balanced that with tools to make getting it right easy.
We also exploited one of Excel’s great features not found in most database entry programs – cut and paste of multiple rows. This helps us pull data from existing sources (such as the web) and easily encorporate it into our systems in the format we need.
Cut and paste is greatly hampered by one of Excel’s methods designed to restrict users from entering data in places they shouldn’t. That method is known as Worksheet Protection. The idea behind Worksheet Protection is solid – prevent users from straying outside the entry area – allow them to change ONLY unprotected cells and nothing else. Unfortunately, when you try to paste a region that overlaps protected cells, Worksheet Protection rejects the entire paste – not just the cells intruding on protected regions. Without using WorkSheet Protection, we overcame that ‘flaw’ in Microsoft’s implementation in the WorkSheet_Change event, but at the price of letting users move anywhere on the Worksheet even to places well outside where entries are supposed to be entered – outside where entries can be checked - outside where values can be updated to the database.
We need to add routines to control how the cursor moves from cell to cell in order to help the user stay in the entry region – to protect them from inadvertantly typing data where their entries will be wasted.
The next posts will deal with this important aspect of database updates.