I am trying to validate user input against data that is in the fourth column
of a worksheet named "data". The amount of rows in this sheet will vary

This is how I receive the input from the user:

Cliname = InputBox(Prompt:="Enter Client Name", Title:="Client Name")

How do I do this?

Thanks in advance.

My first suggestion when input must come from a fixed list would be to use a
listbox or combobox for the user to select, rather than type, the values - it
eliminates the chance of mismatches based on typos, etc and is much easier
and less frustrating for the user, plus you would not need to worry about
validation since you know the answer must be in the list.

Having said that, if you still want to use typed input and validate it, you
can use the Find method to see if the input value is in the column:

Dim Found as Boolean, Cancelled as Boolean

Found = False
Cancelled = False
While (Not Found) And (Not Cancelled)
Cliname = InputBox(Prompt:="Enter Client Name", Title:="Client Name")
Cancelled = (Cliname = False)
Found = Not(Sheets("data").Range("D:D").Find(Cliname) Is Nothing)
If (Not Found) And (Not Cancelled) Then MsgBox "Invalid Client Name; retry"
WEnd

HTH

"lewscannon" wrote:

I am trying to validate user input against data that is in the fourth column
of a worksheet named "data". The amount of rows in this sheet will vary

This is how I receive the input from the user:

Cliname = InputBox(Prompt:="Enter Client Name", Title:="Client Name")

How do I do this?

Thanks in advance.

The easiest way is to use DataValidation in the cell.

otherwise you could do something like this:

Dim y As Integer

On Error Resume Next
y = WorksheetFunction.Match(Cliname, Sheets("data").Range("D:D"), 0)
On Error GoTo 0
If y 0 Then
MsgBox " match"
Else: MsgBox "no match"
End If


--
steveB

Remove "AYN" from email to respond
"lewscannon" wrote in message
...
I am trying to validate user input against data that is in the fourth
column
of a worksheet named "data". The amount of rows in this sheet will vary

This is how I receive the input from the user:

Cliname = InputBox(Prompt:="Enter Client Name", Title:="Client Name")

How do I do this?

Thanks in advance.

A simple match can do this:

If WorksheetFunction.Match(Cliname,Sheets("Data").Ran ge("D:D"),0) = "#N/A"
--
steveB

Remove "AYN" from email to respond
"lewscannon" wrote in message
...
I am trying to validate user input against data that is in the fourth
column
of a worksheet named "data". The amount of rows in this sheet will vary

This is how I receive the input from the user:

Cliname = InputBox(Prompt:="Enter Client Name", Title:="Client Name")

How do I do this?

Thanks in advance.

How would I be able to create a listbox based upon the contents of that
column? The worksheet could contain as many as 30,000 rows, though there
would probably not be more than 5 different selections. Also, the data for
the entire sheet will change periodically, depending on what is copied and
pasted into it (though the column containing the listbox items would remain
in the same place (Column D).

Thanks.

"K Dales" wrote:

My first suggestion when input must come from a fixed list would be to use a
listbox or combobox for the user to select, rather than type, the values - it
eliminates the chance of mismatches based on typos, etc and is much easier
and less frustrating for the user, plus you would not need to worry about
validation since you know the answer must be in the list.

Having said that, if you still want to use typed input and validate it, you
can use the Find method to see if the input value is in the column:

Dim Found as Boolean, Cancelled as Boolean

Found = False
Cancelled = False
While (Not Found) And (Not Cancelled)
Cliname = InputBox(Prompt:="Enter Client Name", Title:="Client Name")
Cancelled = (Cliname = False)
Found = Not(Sheets("data").Range("D:D").Find(Cliname) Is Nothing)
If (Not Found) And (Not Cancelled) Then MsgBox "Invalid Client Name; retry"
WEnd

HTH

"lewscannon" wrote:

I am trying to validate user input against data that is in the fourth column
of a worksheet named "data". The amount of rows in this sheet will vary

This is how I receive the input from the user:

Cliname = InputBox(Prompt:="Enter Client Name", Title:="Client Name")

How do I do this?

Thanks in advance.

Whether you use a listbox, or combobox
Set ListFillRange $A:$A
You can set the properties to only accept a match and the
list will scroll to the closest match

Used DataValidation
Set to List
Source =$A:$A
You can set this to not accept an entry unless it is in the list.

You can do all of these with or without code.

--
steveB

Remove "AYN" from email to respond
"lewscannon" wrote in message
...
How would I be able to create a listbox based upon the contents of that
column? The worksheet could contain as many as 30,000 rows, though there
would probably not be more than 5 different selections. Also, the data for
the entire sheet will change periodically, depending on what is copied
and
pasted into it (though the column containing the listbox items would
remain
in the same place (Column D).

Thanks.

"K Dales" wrote:

My first suggestion when input must come from a fixed list would be to
use a
listbox or combobox for the user to select, rather than type, the
values - it
eliminates the chance of mismatches based on typos, etc and is much
easier
and less frustrating for the user, plus you would not need to worry about
validation since you know the answer must be in the list.

Having said that, if you still want to use typed input and validate it,
you
can use the Find method to see if the input value is in the column:

Dim Found as Boolean, Cancelled as Boolean

Found = False
Cancelled = False
While (Not Found) And (Not Cancelled)
Cliname = InputBox(Prompt:="Enter Client Name", Title:="Client Name")
Cancelled = (Cliname = False)
Found = Not(Sheets("data").Range("D:D").Find(Cliname) Is Nothing)
If (Not Found) And (Not Cancelled) Then MsgBox "Invalid Client Name;
retry"
WEnd

HTH

"lewscannon" wrote:

I am trying to validate user input against data that is in the fourth
column
of a worksheet named "data". The amount of rows in this sheet will vary

This is how I receive the input from the user:

Cliname = InputBox(Prompt:="Enter Client Name", Title:="Client Name")

How do I do this?

Thanks in advance.