Stock Locator Inventory System-Issue with locator

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.

Code:

---

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

---

FoundCell.EntireRow.Delete