Data Validation Macro

  • Thread starter Thread starter Little Penny
  • Start date Start date
L

Little Penny

I trying to create a code the will validate my data in a range of
cells:

B5:D5 is a merged cell

And

E8:E18

I want to insure that the user enters data in this format

XL- and a seven digit number that could start with 0

Example XL-0775412

I also want the XL- to always be capitalized.

If user enters invalid data I want a msg box to give them a sample of
the format

I tried use data validation but I could not get it to work.


I think I would have to use some type of worksheet change event


Any suggestions would be greatly appreciated.
 
I'm not sure about the merged cell (I've not worked with them before), but
for the other range, select the cells in E8:E18 with E8 being the active
cell and then select Custom from the Data Validation dialog box on the
Settings tab and use this formula...

=AND(LEN(E8)=10,LEFT(E8,3)="XL-")

in the Formula field. On the Error Alert tab, make sure that Stop is
selected in the Style field, use a Title something like Invalid Input and
put an message something like the following in the Error Message field...

Your data must start with XL- (X and L being upper case)
and be followed by 7 digits. As an example. XL-1234567

Obviously, change the wording to suit your own personal style.

Rick
 
Back
Top