Validating Data Entry

[Previous] [Next]

Typically, a data entry application will implement some measure of data validation to keep bad data values from getting into the program. In a timesheet system, it makes no sense to allow a user to enter a negative number or a nonnumeric value because these will simply produce errors when you submit the timesheet. Fortunately, you can perform data validation in the Spreadsheet control and keep bad data from getting into your spreadsheet.

To try out the validation, type a negative or nonnumeric value into one of the unlocked timesheet cells. The control will reject the value, flashing an error message in the title bar and playing a sound (if your system is capable of this). The code that makes this happen is quite simple and starts in the Spreadsheet control's EndEdit event handler:

 Sub ssTimesheet_EndEdit(evt)     ' Since the only cells that are editable are those containing the     ' hours worked, assume that the current cell is one of those and     ' therefore should be numeric and greater than zero     ' If the cell is empty, then exit     If Len(trim(evt.EditData)) = 0 Then Exit Sub     ' Check to make sure it's numeric and >= 0     If IsNumeric(evt.EditData) Then         If evt.EditData >= 0 Then             ' Good data--let it go             ' Trick: Set a <bgsound> tag's src property to the             ' name of a wav file to make the browser play the sound             sndBG.src = "cashreg.wav"         Else             ' Data less than zero             displayerror "Hours worked must be greater than zero!"             evt.ReturnValue = False         End If 'Edit data >= 0     Else         ' Data is nonnumeric         displayerror "Hours worked must be a number!"         evt.ReturnValue = False     End If 'Data is numeric End Sub 'Spreadsheet_EndEdit() 

The EndEdit event fires when the user finishes editing a cell, but before that new value is inserted into the spreadsheet and before the spreadsheet has recalculated. The Spreadsheet control passes a few crucial pieces of information in the SpreadsheetEventInfo object passed as the parameter to this event. First, the SpreadsheetEventInfo object exposes a Range property, which during this event specifies the range that the user edited. You can use this to determine which cell was edited, perhaps using a different validation rule for different cell ranges. In this example, the only cells that are unlocked (and therefore editable) are the ones containing the hours worked, so I can assume the edited cell is one of those.

The second crucial piece of information is the EditData property. This property returns the value the user is attempting to enter, and this is the value your code should validate. If your code does not reject EditData, the value in this property will be put into the target cell and the spreadsheet will recalculate based on that new value. In this case, I perform two tests on the value. I first check whether it is numeric, and then I check that it is greater than or equal to 0. If both conditions are true, I accept the data and play the cash register sound. (This always gets a laugh when I am giving a demo.) If either condition fails, I display the appropriate error. The key to denying the edit is to set the ReturnValue property on the SpreadsheetEventInfo object to False. Doing so will cause the spreadsheet to reject the edit and keep the current value in the cell.



Programming Microsoft Office 2000 Web Components
Programming Microsoft Office 2000 Web Components (Microsoft Progamming Series)
ISBN: 073560794X
EAN: 2147483647
Year: 1999
Pages: 111
Authors: Dave Stearns

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net