Sheet Event
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
|