User form do not enter duplicates

[grawls44]

Hi everyone,

I need a little help on this. I have a user form in the field I_Item_code I want it to check the data and see if that code is there ( does that) and pops up a message. now here is what my problem is, it puts the info from the user form in anyway what I want it to do is not put the data in and return to the user form.

I know this has to be simple but at this point my brain is fried. LOL

here is the code that I have.

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Product Data")
Dim rng As Range
Dim lst As Long




'find first empty row
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


lst = ws.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = ws.Range("B4:B" & lst)


'check to see if item code is already in use
With Sheets("Product Data").Range("B:B")
    Set rng = .Find(Me.I_Item_Code.Value, LookIn:=xlValues)
    If Not rng Is Nothing Then
        MsgBox "Item Code In Use"
        Me.I_Item_Code.SetFocus
    End If
End With


'check to see if these fields are empty
If Trim(Me.I_Item_Code.Value) = "" Then
  Me.I_Item_Code.SetFocus
  MsgBox "Please enter a Item Code"
  If Trim(Me.I_Case_Pack.Value) = "" Then
  Me.I_Case_Pack.SetFocus
  MsgBox "Please enter Case Pack"
    If Trim(Me.I_Pack_Size.Value) = "" Then
    Me.I_Pack_Size.SetFocus
    MsgBox "Please enter Pack Size"
      Exit Sub
      End If
    End If
End If


'copy the data to the database
With ws
  .Cells(iRow, 2).Value = Me.I_Item_Code.Value
  .Cells(iRow, 4).Value = Me.I_UPC.Value
  .Cells(iRow, 5).Value = Me.I_Description.Value
  .Cells(iRow, 6).Value = Me.I_Case_Pack.Value
  .Cells(iRow, 7).Value = Me.I_Pack_Size.Value
  .Cells(iRow, 9).Value = Me.I_Pack_Type.Value
  .Cells(iRow, 10).Value = Me.I_Pack_UOM.Value
  .Cells(iRow, 12).Value = Me.I_Case_Tare.Value
  .Cells(iRow, 14).Value = Me.I_Case_Length.Value
  .Cells(iRow, 15).Value = Me.I_Case_Width.Value
  .Cells(iRow, 16).Value = Me.I_Case_Height.Value
  .Cells(iRow, 18).Value = Me.I_Pack_Length.Value
  .Cells(iRow, 19).Value = Me.I_Pack_Width.Value
  .Cells(iRow, 20).Value = Me.I_Pack_Height.Value
  .Cells(iRow, 22).Value = Me.I_Pallet_Ti.Value
  .Cells(iRow, 23).Value = Me.I_Pallet_Hi.Value
  .Cells(iRow, 25).Value = Me.I_Storage_Type.Value
  .Cells(iRow, 26).Value = Me.I_Shelf_Life.Value
  .Cells(iRow, 27).Value = Me.I_Dating_Type.Value
  .Cells(iRow, 28).Value = Me.I_Pack_Tare.Value
  .Cells(iRow, 29).Value = Me.I_Serving_Size.Value
  .Cells(iRow, 30).Value = Me.I_Calories.Value
  .Cells(iRow, 31).Value = Me.I_Cal_From_Fat.Value
  .Cells(iRow, 32).Value = Me.I_Total_Fat.Value
  .Cells(iRow, 33).Value = Me.I_Per_Fat.Value
  .Cells(iRow, 34).Value = Me.I_Saturated_Fat.Value
  .Cells(iRow, 35).Value = Me.I_Per_Saturated_Fat.Value
  .Cells(iRow, 36).Value = Me.I_Trans_Fat.Value
  .Cells(iRow, 37).Value = Me.I_Cholesterol.Value
  .Cells(iRow, 38).Value = Me.I_Per_Cholesterol.Value
  .Cells(iRow, 39).Value = Me.I_Sodium.Value
  .Cells(iRow, 40).Value = Me.I_Per_Sodium.Value
  .Cells(iRow, 41).Value = Me.I_Total_Carbs.Value
  .Cells(iRow, 42).Value = Me.I_Per_Carbs.Value
  .Cells(iRow, 43).Value = Me.I_Dietary_Fiber.Value
  .Cells(iRow, 44).Value = Me.I_Per_Fiber.Value
  .Cells(iRow, 45).Value = Me.I_Sugar.Value
  .Cells(iRow, 46).Value = Me.I_Protein.Value
  .Cells(iRow, 47).Value = Me.I_Per_Vit_A.Value
  .Cells(iRow, 48).Value = Me.I_Per_Vit_C.Value
  .Cells(iRow, 49).Value = Me.I_Per_Calcium.Value
  .Cells(iRow, 50).Value = Me.I_Per_Iron.Value
  .Cells(iRow, 51).Value = Me.I_Contains_Allergens.Value
  .Cells(iRow, 52).Value = Me.I_Type_Allergens.Value
End With


'clear the data
Me.I_Item_Code.Value = ""
Me.I_UPC.Value = ""
Me.I_Description.Value = ""
Me.I_Case_Pack.Value = ""
Me.I_Pack_Size.Value = ""
Me.I_Pack_Type.Value = ""
Me.I_Pack_UOM.Value = ""
Me.I_Case_Tare.Value = ""
Me.I_Case_Length.Value = ""
Me.I_Case_Width.Value = ""
Me.I_Case_Height.Value = ""
Me.I_Pack_Length.Value = ""
Me.I_Pack_Width.Value = ""
Me.I_Pack_Height.Value = ""
Me.I_Pallet_Ti.Value = ""
Me.I_Pallet_Hi.Value = ""
Me.I_Storage_Type.Value = ""
Me.I_Shelf_Life.Value = ""
Me.I_Dating_Type.Value = ""
Me.I_Pack_Tare.Value = ""
Me.I_Serving_Size.Value = ""
Me.I_Calories.Value = ""
Me.I_Cal_From_Fat.Value = ""
Me.I_Total_Fat.Value = ""
Me.I_Per_Fat.Value = ""
Me.I_Saturated_Fat.Value = ""
Me.I_Per_Saturated_Fat.Value = ""
Me.I_Trans_Fat.Value = ""
Me.I_Cholesterol.Value = ""
Me.I_Per_Cholesterol.Value = ""
Me.I_Sodium.Value = ""
Me.I_Per_Sodium.Value = ""
Me.I_Total_Carbs.Value = ""
Me.I_Per_Carbs.Value = ""
Me.I_Dietary_Fiber.Value = ""
Me.I_Per_Fiber.Value = ""
Me.I_Sugar.Value = ""
Me.I_Protein.Value = ""
Me.I_Per_Vit_A.Value = ""
Me.I_Per_Vit_C.Value = ""
Me.I_Per_Calcium.Value = ""
Me.I_Per_Iron.Value = ""
Me.I_Contains_Allergens.Value = ""
Me.I_Type_Allergens.Value = ""


End Sub

any help on this would be great.

Thanks
Greg

 

[Snakehips]

Greg,

Is this what you are wanting?

'check to see if item code is already in use
With Sheets("Product Data").Range("B:B")
    Set Rng = .Find(Me.I_Item_Code.Value, LookIn:=xlValues)
    If Not Rng Is Nothing Then
        MsgBox "Item Code In Use"
        Me.I_Item_Code.SetFocus
        Exit Sub
    End If
End With

Hope that helps.

 

[grawls44]

Thanks Tony

I took a break and figured it out. I also added

Else: on error resume next

Greg