Data Validation

**cg7131** · 08-29-2011, 05:30 PM

Hi,

I'm trying to restrict entry into cells by other users, in that they can only insert alphabet characters (no numbers or characters /,*, &, etc.). I've come across the following formula:

Is Null OR Not Like "*[!a-z]*"

It works, but doesn't allow spaces....how can I format this to allow spaces in the entries?

Thanks,

Christina

**tlafferty** · 08-29-2011, 07:15 PM

If you're evaluating Cell A1:

=OR(CODE(A1)=32,AND(CODE(A1)>=97,CODE(A1)<=122))

Copy and paste validation as needed.

**shg** · 08-29-2011, 07:25 PM

You mean validation via VBA?

    With Range("A1")
        If Not .Value Like "*[!A-Za-z ]*" Then MsgBox "Ok"
    End With

**cg7131** · 08-30-2011, 10:17 AM

Hi,

I was actually just hoping to use the data validation function in Excel, rather than code. I have a complicated macro already running on that spreadsheet, and was hoping to find a more simplistic way to accomplish this through the data validation feature.

**NBVC** · 08-30-2011, 10:43 AM

Is Null OR Not Like "*[!a-z]*"

How did you get that to work then? That is not a formula and cannot be used in Data Validation (I didn't think so anyways)... it can be used in VBA as Shg has shown.

**snb** · 08-30-2011, 11:22 AM

In a macromodule:

 
Function tst(c01 As Range) As Boolean
  tst = not c01.Value Like "*[!A-Za-z ]*"
End Function

put this formula in C1: "=tst(B1)

Datavalidation of B1 (adapted) : "=C1"

**cg7131** · 08-30-2011, 01:34 PM

Hm, it seemed to work when I used the Data Validation feature, selected "custom" and was asked to enter a formula...seemed more straight forward to me.

**NBVC** · 08-30-2011, 02:00 PM

Hmm.. interesting, maybe that is something I never knew you could do?

What exactly did you put in as the actual "working" formula in data Validation?

I entered: Not Like "*[!a-z]*" and it accepted it, but it would error out with anything I tried to enter into the cell.

I guess that formula would allow only single letter entries, no special characters, right?

**ciapul12** · 08-30-2011, 02:26 PM

Hi I was wondering if is possible to create something like this :drop down list with product names, after product is selected from the drop down list it will automatically fill the cells A8,B8,C8,D8,E8 with the settings so I could set a data validation in case if an employee enters incorrect data. This is my first post and I apologize if this post is confusing.
Thanks in advance

Dan

**NBVC** · 08-30-2011, 02:38 PM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.