Check user input

[[email protected]]

I need to check data in a range of 100 cells. The value can be blank or
single character only from these upper case letters (A, B, C, N, P, R, S, T,
X, Y, Z). If the data is not correct prompt user to fix it. Is there better
way to validate the cells that multiple if ..then .. else?

[Ardus Petus]

Data>Validation
Choose List
enter A,B,C,N,P,R,S,T,X,Y,Z (without any quotes) in Source Box
Click OK

Et voila!

HTH
--
AP

"[email protected]"
<[email protected]> a écrit dans le message
de news:[email protected]...
> I need to check data in a range of 100 cells. The value can be blank or
> single character only from these upper case letters (A, B, C, N, P, R, S,
T,
> X, Y, Z). If the data is not correct prompt user to fix it. Is there
better
> way to validate the cells that multiple if ..then .. else?

[Nigel]

Set up data validation (Data->Validation) and in the settings tab, set Allow
drop-down to a List. You need to set up a list of valid values somewhere in
your worksheet. Unfortunately data validation is not case sensitive (!) so
in the past I have used the worksheet change event to replace the value
entered to uppercase. You will need to change the references to those cells
you wish to change and test e.g.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row >= 1 And Target.Row <= 100 Then
Target.Value = UCase(Target.Value)
End If
End Sub


--
Cheers
Nigel



"[email protected]"
<[email protected]> wrote in message
news:[email protected]...
>I need to check data in a range of 100 cells. The value can be blank or
> single character only from these upper case letters (A, B, C, N, P, R, S,
> T,
> X, Y, Z). If the data is not correct prompt user to fix it. Is there
> better
> way to validate the cells that multiple if ..then .. else?

[Nigel]

Ardus solution is even better - I learn something everyday!

--
Cheers
Nigel



"Nigel" <[email protected]> wrote in message
news:%[email protected]...
> Set up data validation (Data->Validation) and in the settings tab, set
> Allow drop-down to a List. You need to set up a list of valid values
> somewhere in your worksheet. Unfortunately data validation is not case
> sensitive (!) so in the past I have used the worksheet change event to
> replace the value entered to uppercase. You will need to change the
> references to those cells you wish to change and test e.g.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Column = 1 And Target.Row >= 1 And Target.Row <= 100 Then
> Target.Value = UCase(Target.Value)
> End If
> End Sub
>
>
> --
> Cheers
> Nigel
>
>
>
> "[email protected]"
> <[email protected]> wrote in message
> news:[email protected]...
>>I need to check data in a range of 100 cells. The value can be blank or
>> single character only from these upper case letters (A, B, C, N, P, R, S,
>> T,
>> X, Y, Z). If the data is not correct prompt user to fix it. Is there
>> better
>> way to validate the cells that multiple if ..then .. else?
>
>