Beyond Excel: VBA and Database Manipulation

Aug 21 2010   7:47PM GMT

Controlling the Cursor



Posted by: Craig Hatmaker
Tags:
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.

 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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: