Duplicate Check :: Text Box Input to Compare with Excel Sheet Content

[Dheepak]

Hi All,

I created a user-form to enter product ID in textbox1. Product ID is unique. There is list of product ID that is already entered in the sheet1 from A2 to A20. I just want to know how to throw a hard-stop vbcritical error if user tries to enter the product ID in the text box which is already available in the range A2:A20.

Any help would be appreciated.

Thanks in advance.

Regards,
Dheepak

 

[tlowry]

The approach I suggest is to not let the user try to add an ID that is already in the data. To do this, disable the button that is used to enter the new user id when the TextBox has such an ID. Here is some code to demonstrate this approach:

1. Open your “test” Excel file (the one with the data in A2:A20)
2. Add a form - altF11, Insert, Form
3. On the form put: A TextBox (TextBox1)and a CommandButton(CommandButton1)
4. Put the code below in the form’s module
5. Change “Data” to the name of the sheet holding the data
6. Save the “test” file
7. Test a bunch

Private Sub TextBox1_Change()
    Const DataSheet = [COLOR=#008000][B]“Data”[/B][/COLOR]
    Me.CommandButton1.Enabled = Sheets(DataSheet).Range("A2:A20").Find(Me.TextBox1, LookIn:=xlValues, _
                                        lookat:=xlWhole, MatchCase:=False) Is Nothing
End Sub
Private Sub UserForm_Initialize()
    Me.CommandButton1.Enabled = False
End Sub

This will give you some idea of the approach I suggest. The next step, is to integrate this approach with the code. (Another post)

“It’s easier to stay out than to get out”