Beyond Excel: VBA and Database Manipulation

Aug 6 2010   5:21PM GMT

Check Entry – Worksheet_Change – Theory

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

There are two times when you want to check entries:
  1. When the user changes something and 
  2. When they want to post entries to the database (aka add, change or delete data). 

Last post covered the second situation.  This covers the first.

Excel provides a rich user interface.  That’s why people like it.  They can do just about anything.  They can move anywhere on the spreadsheet.  Select things.  Move things.  Copy things.   Paste things.  Delete things.  Insert things.

As a developer, we like things controlled.  We want to limit what the user can and cannot do.  We want to guide them carefully through data entry.  We want to check every entry they make.  Control is in direct opposition to the freedom Excel offers, the freedom users love.  Control is required to prevent data corruption.  Striking a balance between control and freedom is tough.  It’s taken me a while to find a balance between the two and the heart of that balance is focused in the WorkSheet_Change event.

Here is the basic flow of this routine:

  • If an entire column is changed (move, paste, insert or delete), the change is thrown out
  • If an entire row is changed and it is in the target entry area, the change is accepted without checking
  • If a cell is locked, any change is removed and the original cell values restored

     -Otherwise_
     

  • First: The system attempts to conform the entry to rules implemented in Set_Entry_Defaults.  An example is converting text to upper case as dictated by values in the Fields Definition Table under the validation columns 
     
  • Second: If this is the first entry in a new row, the system attempts to apply default values for every column.  An example is automatically putting today’s date in an “ENTERED” column and the user’s ID in an “ENTERED BY” column.
     
  • Third: If a single cell is changed and Field Definition Table indicates it is checked by one of the Table Validation rules (XLC, XLT, CUST), the system will check the value using the rule, and if it fails validation, display a Pop-Up validation/selection window to assist the user in selecting something appropriate.  An example is a Pop-Up validation/selection window showing valid Country Codes for a Country Code entry.
     
  • Fourth: If a code or ID entry has associated values that need to be displayed in the entry row, the system attempts to retrieve those values and display them. An example is the Country Name displayed next to the Country Code.
     
  • Fifth: The system performs a final check
     
  • Sixth: The system attempts to move the cursor to the next appropriate field or record based on the key used to exit the cell.

And here is a partial list of how the user might change things and how this routine handles each circumstance:

  • Typing – The most common change is from the user simply typing into a cell, in which case, everything works as it should and nothing special happens.
     
  • Copy/Paste - This is another common situation and one where Excel offers a great benefit.  Sometimes you have data in another spreadsheet, or in a word document, or on a web page – and if only you could highlight it, copy it, and paste it to a table that would upload to the database, complete with full validation.  Wouldn’t that be great!  Well – this routine handles that – but in an odd sort of way.  The problem is that cells copied from other places are pasted, by default, as locked cells – and locked cells can’t be changed by typing, nor are they validated. 

    Another problem with copy/paste is that there is nothing preventing users from pasting into areas outside the entry area (I do NOT use worksheet protection to permit proper copy/paste into rows with some locked cells between fields).  So when cells are pasted, the system 1) Remembers the pasted cells’ values, 2) Undoes the post, 3) Elminates cells outside the entry area, and 4) carefully pastes values (no formats or lock states) from the pasted cells into only unlocked cells.

    This method works great – but adds processing that slows entry.  If your PC is slow, or your users simply never use copy/paste, you can speed data entry by eliminating this capability from your spreadsheet.

  • Inserting rows – This is allowed but not checked.  It will, of course, be checked when the entry is posted.
     
  • Moving Rows – This is allowed but has no impact on the database.  If you want to allow users to ‘sequence lines’, you need to provide a ‘sequence’ field and handle sequencing in code.
      
  • Deleting Rows – This is allowed but has no affect on the database.  Users should be warned to use the “D” in the ACD column to delete rows in the database.
     
  • Inserting columns – If a user wants more fields, they MUST negotiate with the developer (you) to accomodate everything else that goes with that.  If they attempt it, an error is thrown and the insert is removed as though nothing ever happened.  That’s the way it should be.
     
  • Deleting columns – This is not allowed.  If they attempt it, an error is thrown and the deleted column restored as though nothing ever happened.  That’s the way it should be.
     
  • Moving Columns – This is not allowed.  If a user wants the column order changed, for whatever reason, they should see you.  You can easily change the Field Definitions Table to accomodate such requests.  It is also possible to code ‘formats’ to accomodate different users or different ‘line types’ within a record.  But that requires YOU to code.  If a user attempts to move a column, the column is returned to its proper position.  This is as it should be.
     
  • Inserting cells – This really makes no sense in a traditional row oriented data entry scenario.  If it is attempted, the area that was inserted is blanked out and the displaced cells are returned.  If the blanked cells are marked by the Field Definition Table as required, the record(s) will fail validation and the user must set things right before the system will update the database. 

    This is an opportunity for improvement but my cynical nature is holding me back.  If a user is really trying hard to muck things up (or if they are really that poor of judgement), I don’t mind it if the system makes it hard on them to make it right.
       

  • Deleting Cells – Once again, the user is attempting something that doesn’t make sense in a traditional row oriented database.  And once again, this is an opportunity for improvement.

 That’s the theory behind the WorkSheet_Change event code.  In our next post, we’ll discuss the code.

 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: