Data Validation,Restrict Cell Input.

**JapanDave** · 11-27-2011, 09:02 PM

Hey guys,

I have a situation where I need to limit user input. I can't use excels data validation as it is rendered moot when copied over.

I was thinking of an event handler macro that would only allow the criteria that I need to entered. Anything else would bring up an error message and say try again.
Eg, say I want only the characters A, B, C, D, E, F ,G to be entered into a designated range A1:G5 and only 1 character to a cell. If someone enters anything other than these characters then the error appears.
Eg A is OK, But AB is not , B is OK, 1 is not ,Y is not.

I am not sure if this can be done, but I thought I would ask anyway.

Cheers

**mikerickson** · 11-27-2011, 09:05 PM

Data Validation would do that.
What are the details that render it "moot"?

**JieJenn** · 11-27-2011, 09:09 PM

There is no need for VBA.

Highlight Range A1:G5, go to Data > Data Validation > Custom and enter the formula

=AND(OR(A1="A",A1="B",A1="C",A1="D"),LEN(A1)=1)

**mikerickson** · 11-27-2011, 09:14 PM

Another Validation method would be to use List style validation with the source
A,B,C,D,E,F,G

**JieJenn** · 11-27-2011, 09:17 PM

Mike's method is a lot better than mine

**JapanDave** · 11-27-2011, 09:30 PM

Hi Mike,

If someone copies a cell from somewhere else that does not have data validation and pastes it over the data validation cell, it removes the data validation criteria.

**JieJenn** · 11-27-2011, 09:57 PM

Insert the code to the worksheet module

Please Login or Register  to view this content.

**JapanDave** · 11-27-2011, 11:05 PM

Thanks guys,

**grizzly6969** · 11-27-2011, 11:07 PM

JieJenn can this code be used in a name range for data validation

**JapanDave** · 11-28-2011, 12:14 AM

Hey Grizzly,

It can be, just amend the code to say what your named range is.

Please Login or Register  to view this content.

**grizzly6969** · 11-28-2011, 12:56 AM

when I changed range to ("Mylist") it allowed paste in cell possibly not in correct location

Data Validation,Restrict Cell Input.

LinkBack

Thread Tools

Rate This Thread

Display

Data Validation,Restrict Cell Input.

Re: Data Validation,Restrict Cell Input.

Re: Data Validation,Restrict Cell Input.

Re: Data Validation,Restrict Cell Input.

Re: Data Validation,Restrict Cell Input.

Re: Data Validation,Restrict Cell Input.

Re: Data Validation,Restrict Cell Input.

Re: Data Validation,Restrict Cell Input.

Re: Data Validation,Restrict Cell Input.

Re: Data Validation,Restrict Cell Input.

Re: Data Validation,Restrict Cell Input.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions