PDA

View Full Version : Stock Locator Inventory System-Issue with locator



maplebizz
02-18-2018, 08:42 PM
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 (https://www.mrexcel.com/forum/redirect-to/?redirect=https%3A%2F%2Fufile.io%2Fa98ev).


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

SamT
02-19-2018, 12:55 AM
FoundCell.EntireRow.Delete