Data Validation Macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Little Penny - 16 Sep 2007 18:15 GMT

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.

Reply to this Message

Rick Rothstein (MVP - VB) - 16 Sep 2007 19:37 GMT

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

>I trying to create a code the will validate my data in a range of
> cells:
[quoted text clipped - 21 lines]
>
> Any suggestions would be greatly appreciated.

Reply to this Message