Conditional Data Validation - set many constraints in overlapping columns

[EXLwiz]

Hi, can anyone point me to some VBA that does some particular data validation to alert user of errors when:

(for example, these are my cols - headers start on Col D "month")

Col D

Col E

Col F

Col G

Col H

Col I

Col J

Col K

Col L

Col M

Col N

Col O

Col P

Col Q

Col R

Col S

Col T

Col U

Col V

MONTH

STATE

COUNTY

ORDER

TEXT

ALPHA-NUM

DATE

MONEY

MONEY

MONEY

MONEY

MONEY

MONEY

MONEY

MONEY

MONEY

MONEY

MONEY

MONEY

APR

FL

St Lucie

999888777

Romeo

AA123

02/25/2014

$50,000.00

$845.00

$5.00

$25.00

$5.50

$12.36

1. - If E2 is blank and i2 is not, alert user to add a value to E2
2. - Col E cannot contain more than 2 text chars (not allow spaces, numbers, other chars, etc)
3. - Col G cannot contain more than 9 numeric chars (not allow spaces, text, other chars, etc)
4. - cells (rows) in col's E, F and H cannot be blank if that same cell (row) in col G is not blank
5. - use ALL UPPERCASE in cols D:I (except col G, which needs to be 9 number chars -not more or less-, and fyi Col I can allow alpha-numeric mix)
6. - Cols K:V must contain only numeric (money) values (not allow spaces, alpha, other chars, etc)

this last one #6 i already have DV set from the ribbon, but wonder would it be any benefit to move to VBA instead?


fyi, There are no formulas in col's E:V - but col D has like UPPER(TEXT(c2,"MMM")), otherwise there should be no conflicts for the VBA to resolve as to BLANK (ie, we could also be checking for not-nulls if that's a better way).

i already had DV added from ribbon added for STATE col E and col G, but can move all to vba (would prefer to). and, when i added some vba to check the UpperCase, it's running slow thru every cell in the col i guess, and the value of month in col D month was twinkling while the sheet was updating i guess.. can that be avoided by moving all the data validation to vba? or is the vba causing that to happen?


Also the data in this sheet will be used as source for pivot tables on another sheet, if that matters...

and maybe this is a rookie question, but does the workbook HAVE to be saved as type xlsm if using VBA?? i'm suddenly being forced to save with macros enabled but i dont remember having that happen on other vb code i added in the modules.

I appreciate any help!

[aydeegee]

Hi EXLwiz

You ask a lot of questions! I do not have time for all - so if I can just answer the first on, I hope it may help

I have written a worksheet change event for sheet1 (right click on the tab and "View code")

I have written it so that if Col E is not completed when they enter Col I, the cursor goes back to Col E. However, I cannot think of a way of not letting them out of Col E until Col I is complete as they can just tab out of Col E which will not trigger a wroksheet change. Perhaps someone else can help. I have used CF to highlight their error. (I was going to say ineptitude, but I can't spell it)

To answer your last question, Yes the workbook does have to be saved as .xlsm - this started in 2007. I believe it is a way of highlighting the potential risk of this sort of file. (I suppose you could save it as a 97-2003 workbook, but why? and you may lose some functionality.)

Hope this will get you started

Regards
Alastair

[EXLwiz]

thanks i will take a look