Dear thread readers (esp. those new to VBA and macro programming like me)
After more digging through google with the right keywords, I have been successful in finding my answer.
It is as simple as adding; exit sub
(for instance when using an if then
statement place the exit sub
instruction before end if.
Where an if then else
statement is used I added that the field background color should be yellow after the ELSE
(as it was before the field was updated. This way when the user makes the correction in the field it should turn back to yellow to indicate that the new entry is acceptable. Who knows I might use green to be extra anal..lol.
I used Exit Sub
in a message box alerting the user that they had entered invalid data and then set the offending text box to have a red background and set its focus. This way the code to write the data in the field is prevented from running once the user hits OK.
Since there were a few fields on the form that needed to be validated before data was copied to the sheet, it was obviously important, to prevent the procedure to copy the data to the sheet from running if there were numerous junk entries. Each field was validated one procedure at a time and placed above the code that wrote the data, so that all validation would execute first and if there were no erroneous entries, the data in the fields would be copied to the sheet.
By adding exit sub
when an invalid entry was recognized during validation the effect was that as the code executed, one message box would display, take the user back to the field until the field contained acceptable data. If the user hit save and there was another invalid field, the code would then take them back to the other bad field until all fields were filled and contained acceptable entries.
an example of some of the code I created with the exit sub
instruction is as follows, which checks that a field taking the middle three numbers of a phone number is numeric and does not contain less than three numbers. I had already set the max length
for that field to three in the properties box to ensure no more than three numbers could be entered;where txtm3 is the name of the target textbox on the user form
dim mynote as string
dim myanswer as stringIf
IsNumeric(Me.txtM3) = False Or
Len(Me.txtM3) < 3 Then
MyNote = "Non Numeric Value in Middle 3 or Entry Not 3 Numbers Long"
Answer = MsgBox(MyNote, vbCritical + vbOK, "Invalid Entry on Form!")
'set the field red and give it focus
Me.txtM3.BackColor = &H8080FF
me.txtm3.backcolor = &HFFFF& <<<might not be 100% accurate on the exact VBA color model)
thats all folks.
Hope this helps anyone with similar end goals.
Darren De Wilde
Edited by Darren De Wilde, 27 September 2010 - 08:57 PM.