Data Validation and VBA

[zico8]

Hi,
I have one cell with Data Validation - list that allow choose only values "AAA", "BBB", "CCC".
The value can by typed or scanned from barcodes. Scanning works rather like pasting so excel does not display alert if value is "DDD".

This is OK for me but I do not want to input data if scanned barcode will be different then 3 char length.

Below code does the job, but the problem is during typing instead scanning. Due to data validation checking, excel remove typed value so my msgbox is executed twice.

How to handle this?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, cells(1,1)) Is Nothing Then        
If Len(Target.Value) <> 3 Then
            MsgBox "Incorrect value!"
            Application.EnableEvents = False
                Target.Value = ""
                Target.Select
            Application.EnableEvents = True
        Exit Sub
        End If
    End If
End Sub

 

[ranman256]

skip If the cell is not 3...

Private Sub Worksheet_Change(ByVal Target As Range)
if len(activecell.value)=3 then
   If Not Intersect(Target, cells(1,1)) Is Nothing Then        
     ' ...
   endif
endif
end sub

 

[zico8]

Thanks ranman256,

I do not think this is correct solution.
If lenght of scanned value will be different than 3 neither macro nor data validation react.