Hi All,

I have an issue to make my Userform locate new item added to correct locator. What i have was each my Trolley will locate 30 Trays, and each Trays will locate maximum 8pcs of items. When i perform try to add/delete certain Serial Number, it will add/delete entire row include my Locator which is Trolley and Tray Column which is i set it as permanent. Do you guys have any idea to fix this problem?

Attach below my macro spreadsheet.

Spreadsheet Here.

Option Base 1Dim FoundCell As Range
Dim Search As String
Dim ws As Worksheet




Private Sub CommandButtonAdd_Click()
    
    Search = Trim(Me.TextBoxSN.Value)
    If Len(Search) = 0 Then Exit Sub
    If Application.CountIf(ws.Columns(1), Search) > 0 Then
        
        MsgBox Search & Chr(10) & "Record Exists", 16, "Record Exists"
        
    Else
        On Error GoTo exitsub
        Set FoundCell = ws.Cells(ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 2, 1)
        With FoundCell
            .Value = Search
            For i = 1 To 6
                .Offset(, i).Value = Me.Controls(ControlsArr(i)).Text
                Next i
            End With
            EnableButtons True
        End If
        
        MsgBox Search & Chr(10) & "New Record Added", 48, "Record Add"
        
exitsub:
    Me.TextBoxSN.SetFocus
    If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub
    
Private Sub CommandButtonDelete_Click()
    Dim Response As VbMsgBoxResult
    If Not FoundCell Is Nothing Then
        Response = MsgBox(Search & Chr(10) & "Do You Want To Delete Record?", 36, "Delete Record")
        If Response = vbNo Then Exit Sub
        FoundCell.EntireRow.Delete
        Search = ""
        Set FoundCell = Nothing
    Call CommandButtonClear_Click
    End If
End Sub
    
Private Sub CommandButtonupdate_Click()
    Dim i As Integer
    For i = 1 To 6
        FoundCell.Offset(, i) = Me.Controls(ControlsArr(i)).Text
    Next i
    MsgBox Search & Chr(10) & "Record Updated", 48, "Record Updated"
    Me.TextBoxSN.SetFocus
End Sub
        
Private Sub SearchButton_Click()
            
    On Error GoTo exitsub
'validate text box
    Search = Trim(Me.TextBoxSN.Value)
    If Len(Search) = 0 Then
        MsgBox "Please Enter SN", 48, "Search"
    Else
        Set FoundCell = ws.Columns(1).Find(Search, LookIn:=xlValues, lookat:=xlWhole)
        If Not FoundCell Is Nothing Then
            For i = 1 To 6
                Me.Controls(ControlsArr(i)).Text = FoundCell.Offset(, i)
            Next i
            EnableButtons True
        Else
            EnableButtons False
            MsgBox "Serial Number Tidak Wujud", 48, "Not Found"
        End If
    End If
                
exitsub:
    Me.TextBoxSN.SetFocus
    If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub
            
Private Sub TextBoxSN_Change()
    If Len(Me.TextBoxSN.Text) = 0 Then Call CommandButtonClear_Click
End Sub
            
            
Private Sub UserForm_Initialize()
    Set ws = ThisWorkbook.Sheets("sheet1")
    EnableButtons False
    Me.TextBoxSN.SetFocus
End Sub
            
Private Sub CommandButtonClear_Click()
    For i = 1 To 6
     Me.Controls(ControlsArr(i)).Text = ""
    Next i
    Me.TextBoxSN.SetFocus
    EnableButtons False
End Sub
                
Private Sub CommandButtonCancel_Click()
    Unload Me
End Sub
                
Function ControlsArr() As Variant
    ControlsArr = Array("PNComboBox", "ComboBoxDesc", "ComboBoxStatus", "TextBoxRemarks", "Trolley", "Tray")
End Function




Sub EnableButtons(ByVal state As Boolean)
    Me.CommandButtonDelete.Enabled = state
    Me.CommandButtonupdate.Enabled = state
End Sub