checking for unique values in user forms in VBA

[VBAmalta89]

i have a user form which contains a text box where the user has to enter a component number. This number is then stored in a worksheet in a particular column (all numbers inputted in the field are stored under each other in column A on worksheet 1)

All the numbers inputted must be unique therefore I need to create a procedure that will check whether the number inputted in the text box already exists in column A on worksheet 1 and prompts if the component number is not unique.

how do u do this?

 

[VoG]

Perhaps like this

If IsNumber(Application.Match(Val(textbox1.Value), Sheets("Sheet1").Columns("A"), 0)) Then
'
'it is a duplicate
'
End If

 

[VBAmalta89]

and how do i make the program re enter the number rather than proceeding?

THis is because when the button OK is clicked i want it to prompt the user that the number is not unique and force them to re enter the number.

 

[Norie]

Do you want the numbers to run in sequence at all?

Or is it totally up to the user to enter it, and it will be accepted as long as it's unique?

 

[VBAmalta89]

i want it to be accepted as long as it is unique in column A of worksheet 1. (the value start in cell A7 and proceed downwards in column A in no particular order)

 

[VoG]

Try something like

Do While IsNumber(Application.Match(Val(textbox1.Value), Sheets("Sheet1").Columns("A"), 0))
    MsgBox "Duplicate!", vbExclamation
    textbox1.Value = ""
    textbox1.SetFocus
Loop

 

[VBAmalta89]

@VoG

The code u gave me isnt working. Its giving me an error...what is the IsNumber statement in the code u gave me as thats wer the error seems to be?

 

[VoG]

Sorry, it should be IsNumeric.