I have a sheet which is sent to users to check data on it and also add
further data to it.

Column F on the sheet has a validation list, it is made up of 8 items.

The first 4 (Yes1, Yes2, NI and No) are to be used for the data that is on
the sheet when it is sent, the last 4 (Yes1^, Yes2^, NI^ and No^) are used
for any additional data that is added.

I would like a way to ensure that the correct item is picked. For example,
any additional date should have a ^ and original data shouldn't.

Column A is called 'ID' and is simply 1, 2, 3, 4, 5, etc, etc.
Sheets("Sheet2").Range("F22").Value = the number of rows of original data.

When the user chooses an item in the list is there a way to check if the ID
is <= Sheets("Sheet2").Range("F22").Value , if it is then the entry should
be Yes1, Yes2, NI or No? If it isn't , MsgBox "Error!"

Similarly, when the user chooses an item in the list is there a way to check
if the ID is Sheets("Sheet2").Range("F22").Value , if it is then the entry
should be Yes1^, Yes2^, NI^ or No^? If it isn't , MsgBox "Error!"

I hope I have explained clearly and that this 'validation check' is possible
in this way.

Thanks in advance.

Gareth

I have no experience when it comes to sheet code but have come up with the
following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 Then
If Target.Offset(0, -5).Value <= Worksheets("Sheet2").Range("F22").Value
Then
If Right(Target.Value, 1) = "^" Then
MsgBox "Error, you must choose one without a roof on!"
End If
End If
End If
If Target.Column = 6 Then
If Target.Offset(0, -5).Value Worksheets("Sheet2").Range("F22").Value
Then
If Right(Target.Value, 1) < "^" Then
MsgBox "Error, you must choose one with a roof on!"
End If
End If
End If
End Sub

It seems to do what I want but I wondered if anyone to simplify or improve
the code.

Many thanks.

Gareth

"Gareth" wrote in message
...
I have a sheet which is sent to users to check data on it and also add
further data to it.

Column F on the sheet has a validation list, it is made up of 8 items.

The first 4 (Yes1, Yes2, NI and No) are to be used for the data that is on
the sheet when it is sent, the last 4 (Yes1^, Yes2^, NI^ and No^) are used
for any additional data that is added.

I would like a way to ensure that the correct item is picked. For
example,
any additional date should have a ^ and original data shouldn't.

Column A is called 'ID' and is simply 1, 2, 3, 4, 5, etc, etc.
Sheets("Sheet2").Range("F22").Value = the number of rows of original data.

When the user chooses an item in the list is there a way to check if the
ID
is <= Sheets("Sheet2").Range("F22").Value , if it is then the entry should
be Yes1, Yes2, NI or No? If it isn't , MsgBox "Error!"

Similarly, when the user chooses an item in the list is there a way to
check
if the ID is Sheets("Sheet2").Range("F22").Value , if it is then the
entry
should be Yes1^, Yes2^, NI^ or No^? If it isn't , MsgBox "Error!"

I hope I have explained clearly and that this 'validation check' is
possible
in this way.

Thanks in advance.

Gareth

Gareth,

If I understand correctly this should do it.

I have assumed that the data list is F1:F8, and that the ID is A1, change to
suit.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit

If (Not Intersect(Target, Range("F1:F4")) Is Nothing) Then
If Range("A1") <= Worksheets("Sheet2").Range("F22") Then
If Right(Target.Value, 1) = "^" Then
MsgBox "Error!"
End If
ElseIf Right(Target.Value, 1) < "^" Then
MsgBox "Error!"
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

As worksheet event code it gopes in the worksheet code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
...
I have a sheet which is sent to users to check data on it and also add
further data to it.

Column F on the sheet has a validation list, it is made up of 8 items.

The first 4 (Yes1, Yes2, NI and No) are to be used for the data that is on
the sheet when it is sent, the last 4 (Yes1^, Yes2^, NI^ and No^) are used
for any additional data that is added.

I would like a way to ensure that the correct item is picked. For
example,
any additional date should have a ^ and original data shouldn't.

Column A is called 'ID' and is simply 1, 2, 3, 4, 5, etc, etc.
Sheets("Sheet2").Range("F22").Value = the number of rows of original data.

When the user chooses an item in the list is there a way to check if the
ID
is <= Sheets("Sheet2").Range("F22").Value , if it is then the entry should
be Yes1, Yes2, NI or No? If it isn't , MsgBox "Error!"

Similarly, when the user chooses an item in the list is there a way to
check
if the ID is Sheets("Sheet2").Range("F22").Value , if it is then the
entry
should be Yes1^, Yes2^, NI^ or No^? If it isn't , MsgBox "Error!"

I hope I have explained clearly and that this 'validation check' is
possible
in this way.

Thanks in advance.

Gareth