One of the columns in the spread sh

[bnageshrao]

One of the columns in the spread sheet is used to input Zip code. The user should input either 5 or 9 digits. How do you write VBA to make sure either they enter 5 digits and if not 9 digits. It should not be anything else other than 5 or 9 digits.

 

[DarkSun]

Have a play with Data-Validation you should be able to do this.

 

[bnageshrao]

With data validation I tried and it flags only when it is less than 5 or greater than 9 but not for in between, I will try again meanwhile if u figure it out let me know. Thanks.

 

[Klae]

Data Validation can do it here's how:

select the column you want, in my example I'm using column A. Then go to data | validation and choose Custom in the ALLOW drop down.
In the FORMULA drop down enter this formula:
=IF(LEN(A1)=5,TRUE,IF(LEN(A1)=9,TRUE)) and then you can only enter 5 or 9 characters.

 

[DarkSun]

Hello, Klae.

An easier formula would be:

=OR(Len(A1)=5,Len(A1)=9)

 

[Klae]

DarkSun,
I tried that one but it didn't work!

 

[DarkSun]

Try it again and it will. The Formula just needs to return True or False for the validation to work.

 

[bnageshrao]

Both Codes works and thanks a lot my friends.