Jump to content


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.

Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.


Excel VBA

  • Please log in to reply
1 reply to this topic

#1 Darren De Wilde

Darren De Wilde

  • Members
  • 208 posts
  • Location:Howell, NJ
  • Local time:01:56 AM

Posted 25 September 2010 - 09:24 PM

Hi Everyone,

I am a bit stumped on writing code to check fields on a form before writing the contents of the fields to the appropriate cells.

The form and the worksheet are relatively straightforward with basic data as it is for logging cold call results as they are entered into the form. I have tested the form and it does at least write the data as expected when save is clicked. Then in good form I thought I would get into the habit of error trapping and started creating instances where the a number field would contain a letter, etc. etc.

I have coded so far so that the field containing invalid data would cause the field to turn red. The problem is, that if I add the code to check the contents of the required fields in the click section of the save button, it checks the fields, displays an error message and even sets focus on the offending field but still writes the data to the worksheet anyway. I then tried to create the data checking when the required field to check was updated.

I have almost got it to work but then found that if several fields contained errors, the save button would remain not enabled even after correcting the issue.

My question is this;

how can i write code to check the validity of entries in a form before the data is written to the worksheet regardless if there are multiple problems or one problem, stop the data from being written until all fields are valid. I need to check that a name field does not contain alphanumeric data also.

so far I only know how to use

[b]if isnumeric(field) = false or true

I also need to write code to check that there are 3 numbers in one field and 4 in another (the form is set up to take phone numbers as area code, middle three and last four in separate text boxes. The area code is a combo box to minimize errors in data entry.)

I am sure there is a more efficient way to check all fields in the form before writing data to the worksheet.

thank you or all your answers, insight and time

Darren De Wilde

I considered writing this thread in the business applications area but was not sure since it is kind of logic based and maybe best answered by a programmer or someone who knows VBA. I hope I have not made the code too ambiguous but I did search the net for a while.

thanks so much

BC AdBot (Login to Remove)


#2 Darren De Wilde

Darren De Wilde
  • Topic Starter

  • Members
  • 208 posts
  • Location:Howell, NJ
  • Local time:01:56 AM

Posted 27 September 2010 - 07:58 PM

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 string

If IsNumeric(Me.txtM3) = False Or Len(Me.txtM3) < 3 Then

MyNote = "Non Numeric Value in Middle 3 or Entry Not 3 Numbers Long"

'Display MessageBox
Answer = MsgBox(MyNote, vbCritical + vbOK, "Invalid Entry on Form!")

'set the field red and give it focus
Me.txtM3.BackColor = &H8080FF
Exit Sub
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.
Thread Closed

Darren De Wilde

Edited by Darren De Wilde, 27 September 2010 - 08:57 PM.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users