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