vbOK, IF statement met

[dversloot1]

Hello,

I'm using a user form to allow users to enter a new Product ID for a New Product Form. I want to stop the user from using a Product ID that is already in use. The code seems to stop any number inputted into the NewID input box.
Any idea why?

Private Sub CreateNewQMA_Click()
Dim Question As String


Question = "ID already exists. Please choose another."


If IsError(Application.Match(NewID.Value, Sheets("Data").Range("B1:B2000"), 0)) Then
If MsgBox(Question, vbOK, "Double Checking") = vbOK Then
NewID.Value = ""
End If


Exit Sub


Else


End If


ClearFields
Range("C3") = NewID.Value
Unload LoadForm
End Sub

 

[CodeNinja]

This looks a bit backwards to me... It looks like if you do NOT find the match (which would throw the error you catch with If IsError), you say ID already exists (which it does not because there was no match) and exit sub. If you find the match, you then clear fields and make c3 = the id...

Am I reading this wrong, or are you backwards here?

 

[AlphaFrog]

This returns TRUE if it does not already exist.
If IsError(Application.Match(NewID.Value, Sheets("Data").Range("B1:B2000"), 0)) Then

I think you want this...
If Not IsError(Application.Match(NewID.Value, Sheets("Data").Range("B1:B2000"), 0)) Then

 

[dversloot1]

It wasn't noticing the difference between IF and IF NOT. I made some alterations to it using the FIND functions rather than the MATCH function and came up with this:

Private Sub CreateNewQMA_Click()
Dim Question As String
Dim foundrng As Range


Set findrng = Sheets("Data").Range("B1:b2000")
Set foundrng = findrng.Find(NewID.Value)


Question = "ID already exists. Please choose another."


If Not (foundrng Is Nothing) Then
MsgBox (Question)


Exit Sub


Else


End If


ClearFields
Range("C3") = NewID.Value
Unload LoadForm
End Sub

Worked this way.

 

[AlphaFrog]

Match probably didn't work because NewID.Value is a text string and Range("B1:B2000") are numeric values.

 

[AlphaFrog]

...oops

 

[dversloot1]

I see. Thanks AlphaFrog