PDA

View Full Version : Form created in worksheet, using macro to submit the info to second sheet



mh2013
03-09-2018, 08:14 PM
I have created a fillable form set up using a simple worksheet.(not the form in excel) I have locked all cells that should be left alone and created a "submit" button to copy this info to another sheet and clear the cells so another one may be completed. I need to set up If Then Else function so if any required fields are missed it gives an error msg box. I have this done, but it is very large. How can I set it up so that the cells for things such as name, address, city, etc. all give the same error msg so I don't have so many if functions going on? And how can I write an if function to error if the state is not given in 2 characters or the zip code is not 5 characters?

werafa
03-10-2018, 01:10 AM
set a string variable to contain your message.
you can also join strings to form a message using the '&'.

so, msgbox(myString1 & myString2) could be a generic message, followed by something specific

use worksheet events to trigger specific checks after data is entered.
use len (eg. strLength = Len(myDate)) to check string length