UserForm to Edit and Delete Inventory System

[maplebizz]

Hi Guy's

I am new to VBA, this is was my first trial on programming. This was inventory system to detect location and status of my item. I have several problem can't solve so far.

1) Un able to search by SN and update/edit when using button on Userform
2) Problem to delete cells on Coloum A(SN) on particular Row only, what i found so far on internet need to delete entire Row.

Spreadsheet

Dashboard ScreenShot

Private Sub UserFormBWIP_Initialize()


'Empty TextBoxSN
TextBoxSN.Value = ""


'Empty TextBoxRemarks
TextBoxRemarks.Value = ""


'Set Focus on TextBoxSN
TextBoxSN.SetFocus


End Sub




Private Sub CommandButtonAdd_Click()






'UpdatebyExtendoffice20161222


      Dim x As Integer
      Application.ScreenUpdating = False
      
      ' Set numrows = number of rows of data.


      NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count + 1
      
      ' Select cell a1.
      Range("A1").Select
      
      ' Establish "For" loop to loop "numrows" number of times.
      For x = 1 To NumRows
         ' Insert your code here.
         
         'Determine emptyRow




'Transfer information
Cells(NumRows, 1).Value = TextBoxSN.Value
Cells(NumRows, 2).Value = PNComboBox.Value
Cells(NumRows, 3).Value = ComboBoxDesc.Value
Cells(NumRows, 4).Value = ComboBoxStatus.Value
Cells(NumRows, 5).Value = TextBoxRemarks.Value
         
         
         
         ' Selects cell down 1 row from active cell.
         ActiveCell.Offset(1, 0).Select
      Next
      Application.ScreenUpdating = True
      
      
      Call UserFormBWIP_Initialize
      
End Sub






Private Sub CommandButtonClear_Click()


Call UserFormBWIP_Initialize


End Sub


Private Sub CommandButtonCancel_Click()
 
 Unload Me
 
End Sub




Private Sub SearchButton_Click()
Dim ws As Worksheet
Dim strSearch As String
Dim aCell As Range, v


On Error GoTo Err


'validate text box
v = Trim(TextBoxSN.Value)
If Len(v) = 0 Then
    MsgBox "Please Enter SN"
    Cancel = True
    Me.TextBoxSN.SetFocus
    Exit Sub
End If


Set aCell = Sheets("sheet1").Range("A:A").Find(v, lookat:=xlWhole)


If Not aCell Is Nothing Then


    With aCell.EntireRow


        Trolley.Text = .Cells(, "F").Value
        Tray.Text = .Cells(, "G").Value
        PNComboBox.Text = .Cells(, "B").Value
        ComboBoxDesc.Text = .Cells(, "C").Value
        ComboBoxStatus.Text = .Cells(, "D").Value
        TextBoxRemarks.Text = .Cells(, "E").Value


    End With


    Me.TextBoxSN.Visible = True
    Me.TextBoxSN.Visible = True


Else
    MsgBox "Serial Number Tidak Wujud"
    Cancel = True
    'UserFormBWIP.TextBoxSN.Value = "" 'don't do this!
    TextBox6.SetFocus
End If


Exit Sub


Err:
    MsgBox Err.Description
End Sub

 

[maplebizz]

try replacing this line

Set FoundCell = ws.Cells(ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1, 1)

with this

Set FoundCell = ws.Cells(ws.Range("A2").End(xlDown).Row + 1, 1)

Dave

Hey Dave,

That's Work!.
Thanks you very much....

I might be study more for condition formatting for each coloumn key in by user and make my macro password protected.

Thanks again.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...ator-inventory-system-issue-with-locator.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.