Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation Macro
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation Macro
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 "Little Penny" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get data validation message to appear using a macro? | Excel Programming | |||
Speeding up a Data Validation macro | Excel Programming | |||
validation warning for macro data | Excel Programming | |||
Data Validation and Macro | Excel Programming | |||
Macro on a data validation list | Excel Programming |