Hi
one way: change your code as follows:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim LRange As Range
Set LRange = Range("B:B")
If Len(Target) 27 Then
MsgBox "The value you entered is greater than 27 characters"
End If
If Application.worksheetfunction.countif(LRange,targe t.value)1 then
msgbox "Duplicate"
end if
End Sub

Some note:
- I would add some more error checking to your code (checking if column
B is affected, that only one cell is changed, etc.)
- You don't need VBA for this checking for duplicates and lenght. Why
don't you use data validation. e.g. for duplicates enter the following
validation formula in 'Data - Validation' for cell B1:
=COUNTIF($B:$B,B1)=1
oor for testing the length:
=len(B1)<=27


HTH
Frank

I am trying to use VBA to check a new value in a cell against other
values in a column to alert the user as to whether it is a duplicate.
I would like to do this using a Message Box.

I already have code to check the length of the new value and would
like code that does something similar.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim LRange As Range

Set LRange = Range("B:B")
If Len(Target) 27 Then
MsgBox "The value you entered is greater than 27 characters"
End If

End Sub

I am using Excel 97.

Thanx,
Alice.


---
Message posted from http://www.ExcelForum.com/