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

 

[dmt32]

Hi welcome to the forum,

Place all this code in UserformBWIP code page

Option Base 1
Dim 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 + 1, 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

Note the Variables at the top - these MUST site at the very TOP of your forms code page OUTSIDE any procedures.

I have not had lot time to spend on this or fully tested it but hopefully, will give you some ideas to work with.

BTW you should not rename event code

Adding the UserForms name is not required

Private Sub UserFormBWIP_Initialize()


End Sub

Leave event codes with their original names.

Dave

 

[maplebizz]

Hi Dave,

Thanks a lot for your guide. Now my code in good condition structure and more clear to read.

I did try the code you was share above. Yes, all my buttons now WORKS!

However,

The idea is, when people add in new Serial Number to the database, it will automatically locate in TROLLEY and TRAY which is have first EMPTY.
My Column F and G are permanent as a guide for locator. Each of my TRAYS can keep maximum 8pcs SERIAL NUMBER of Item only.

For now, if i add or delete any SN, entire row will update including my Column F and G.

UserFormBWIP




Do you any idea to make this idea works?

 

[dmt32]

Hi,

Try replacing this line:

FoundCell.EntireRow.Delete

with this

FoundCell.Resize(, 5).Delete shift:=xlShiftUp

and see if this does what you want

Dave

 

[maplebizz]

Hi,

Try replacing this line:

FoundCell.EntireRow.Delete

with this

FoundCell.Resize(, 5).Delete shift:=xlShiftUp

and see if this does what you want

Dave

Its still delete entire row, i did replace the code as you suggested.
Row below that also shift up.

 

[dmt32]

just tested suggestion & worked ok for me.
The row in Columns A - E were deleted & shifted up
Columns F - G were left unchanged.

Dave

 

[maplebizz]

just tested suggestion & worked ok for me.
The row in Columns A - E were deleted & shifted up
Columns F - G were left unchanged.

Dave

Dave,

Yes, that's absolutely work. But row below still will shift up after that mention cell was delete.
I would like to make it blank to let new next item added will fill in that empty space.

Regards,
Ash

 

[dmt32]

Dave,

Yes, that's absolutely work. But row below still will shift up after that mention cell was delete.
I would like to make it blank to let new next item added will fill in that empty space.

Regards,
Ash

change this

FoundCell.Resize(, 5).Delete shift:=xlShiftUp

to this

FoundCell.Resize(, 5).ClearContents

and see if this now does what you want


Dave

 

[maplebizz]

change this

FoundCell.Resize(, 5).Delete shift:=xlShiftUp

to this

FoundCell.Resize(, 5).ClearContents

and see if this now does what you want


Dave

Yes Dave,

This is exactly i want. It's work. But new data added not found first empty cells on Column A to use.
It will add to last row in the list.
The empty cells delete before remain clear.

Regards,
Ash

 

[dmt32]

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