Accepting unique values in user form

[VBAmalta89]

I have a user form where the user is asked to input a component number into a text box. these numbers are then stored in column A in worksheet 1.

I require each of these component numbers entered to be unique. So far i have managed to achieve something close to this by using the following code:

Do While IsNumeric(Application.Match(Val(compnum.Value), Sheets("Component Information").Columns("A"), 0))
MsgBox "The component number entered is not unique!", vbExclamation
compnum.Value = ""
compnum.SetFocus
Loop

however, the problem is that while it does prompt me that the number isnt unique, it still accepts all the other data inputted and inputs it into the worksheet with a blank component number.

What i want is that it doesnt allow the user to proceed when pressing Ok in the user form until the component number entered is unique.

Any ideas how i can solve this problem?

 

[daverunt]

Hi,

something along these lines.
It's untested so there may be errors.

ValidEntry = False
 
Do 
 
If IsNumeric(Application.Match(Val(compnum.Value), Sheets ("Component Information").Columns("A"), 0))
 
ValidEntry = True
 
End If
 
Else
MsgBox "The component number entered is not unique!", vbExclamation
compnum.Value = ""
compnum.SetFocus
End If
 
 Loop Until ValidEntry