Search,Edit,Add,Delete userform

Hello,

I am trying to design a user form using a sample workbook I found on the forum to Search,Edit,Add,Delete a table within a worksheet.

The table is called table5 and has 9 columns

I've modified the code however I'm getting 'Subscript ou of range' error

Option Explicit
Dim sc As Long, i As Long
Dim answer As VbMsgBoxResult
Dim LookupValue As String
Dim ws1 As Worksheet
Dim tbl1 As ListObject
Dim FoundCell As Range
Dim newrow As ListRow
Dim response As VbMsgBoxResult

Private Sub txtbox1_AfterUpdate()
With lbo1
For i = 0 To .ListCount - 1
If .List(i, 0) = txtbox1.Text Then
txtbox1 = .List(i, 0)
txtbox2 = .List(i, 1)
txtbox3 = .List(i, 2)
txtbox4 = .List(i, 3)
txtbox5 = .List(i, 4)
txtbox6 = .List(i, 5)
txtbox7 = .List(i, 6)
txtbox8 = .List(i, 7)
txtbox9 = .List(i, 8)
sc = i + 2
Exit For
End If
Next i
End With
End Sub

Private Sub lbo1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If Me.lbo1.ListIndex = -1 Then Exit Sub
If Me.lbo1.ListIndex > -1 Then sc = Me.lbo1.ListIndex + 2
ClearForm
Set ws1 = Sheet1
Set tbl1 = ws1.ListObjects("Table5")
With tbl1
For i = 1 To 9
Controls("txtbox" & i).Value = .Range(sc, i)
Next i
End With
End Sub
Private Sub cmdadd_Click()
'check for a serial number
If Trim(Me.txtbox4.Value) = "" Then
Me.txtbox4.SetFocus
MsgBox "Please enter a Serial number"
Exit Sub
End If
Set ws1 = Sheet1
Set tbl1 = ws1.ListObjects("Table5")
LookupValue = Me.txtbox4.Value
On Error Resume Next
Set FoundCell = tbl1.DataBodyRange.Columns(5).Find(LookupValue, LookAt:=xlWhole)
On Error GoTo 0
If Not FoundCell Is Nothing Then
answer = MsgBox("This Serial Number already in file." & vbCrLf & "Do you want to add a duplicate", vbQuestion + vbYesNo + vbDefaultButton2, "another ?")
If answer = vbNo Then
Me.txtbox4.Value = ""
Me.txtbox4.SetFocus
Exit Sub
End If
End If
Set newrow = tbl1.ListRows.add
With newrow
For i = 1 To 9
.Range(i) = Controls("txtbox" & i).Value
Next i
End With
ClearForm
LoadListBox
End Sub

Private Sub cmdclear_Click()
MsgBox ("This action only clears the form NOT the record" & vbCrLf & "Ready for adding NEW entry."), vbOKOnly, "Clear Form "
ClearForm
End Sub

Private Sub cmddelete_Click()
If Me.txtbox4 = "" Then
MsgBox "No Item selected", , "Errors"
Exit Sub
End If
response = MsgBox("ARE YOU CERTAIN YOU WISH TO REMOVE RECORD ?", vbCritical + vbYesNo + vbDefaultButton2, "Remove Record")
If response = vbNo Then
ClearForm
Exit Sub
End If
Set ws1 = Sheet1
Set tbl1 = ws1.ListObjects("Table5")
tbl1.ListRows(sc - 1).DELETE
ClearForm
LoadListBox
MsgBox (" RECORD REMOVED"), vbOKOnly + vbInformation, "Record Removed"
End Sub

Private Sub cmdedit_Click()
If Me.txtbox4 = "" Then
MsgBox "No Item selected", , "Errors"
Exit Sub
End If
Set ws1 = Sheet1
Set tbl1 = ws1.ListObjects("Table5")
With tbl1.ListRows(sc - 1)
For i = 1 To 9
.Range(i) = Controls("txtbox" & i).Value
Next i
End With
ClearForm
LoadListBox
MsgBox ("Details Changed"), vbOKOnly + vbInformation, "SAVED"
End Sub

Private Sub UserForm_Initialize()
ClearForm
LoadListBox
End Sub

Private Sub cmdclose_Click()
ThisWorkbook.Save
Unload Me
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the Close Form button!"
End If
End Sub

Can anyone help?
Thanks

Add NEW entry to Database:

Option Explicit
Dim ctl
Dim list As ListObject
Dim n As Long, f As Long, X As Long
Dim Cell As Range, Cell2 As Range, oneCell As Range
Dim sh As Worksheet, sheet As Worksheet
Dim config As Worksheet

Private Sub cbutNEW_Click()

'Add new whatever to next free row on Database


'Stop screen jumping
  
    Application.ScreenUpdating = False
    
'Remove filter if applied to Database sheet (assumed Database is sheet 1):
    
    If Table5.FilterMode Then
    Table5.ShowAllData
    
    With Table5

'Find first free row

    n = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    .Cells(n, 1).Value = tbox or cbox whatever.Value
    .Cells(n, 2).value = tbox or cbox whatever.Value
    .Cells(n, 3) .value= tbox or cbox whatever.Value

     etc. . .
    
'If you want to resort Database alphabetically, assuming data is in Table format:

   Table5.Select
    .Range("Table5[[#Headers],[WHATEVER]]").Select
    .Range(Selection, Selection.End(xlDown)).Select
    .Range("Table5[#All]").Select
    ActiveWorkbook.sheet1.ListObjects("Table1").Sort.SortFields. _
        Clear
    ActiveWorkbook.sheet1.ListObjects("Table1").Sort.SortFields. _
        Add2 Key:=Range("Table5[WHATEVER]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.sheet1.ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    aPpLICATION.ScreenUpdating = True
    
    End With
   
End Sub

FIND an existing entry and upload data to User Form, assuming Database is Sheet 1:

Private Sub cbutFIND_Click()

    application.ScreenUpdating = False
    
'Find whatever in Database

'Find last record
    Table5.Select
    With tABLE5
    n = .Cells(.Rows.Count, "A").End(xlUp).Row
    
'Select data in Col A (assuming that is where the Match has to be made?)

   .Range("A2:A" & n).Select
           
'Find whatever in the list

    Set Cell = Selection.Find(What:=tbox or cbox whatever.Value, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

    If Cell Is Nothing Then
    Exit Sub

    Else
    
'Copy active row into User Form
    X = Cell.Row
    
    tbox or cbox whatever = .Cells(X, whichever column matches)
    tbox or cbox whatever = .Cells(X, whichever column matches)
    tbox or cbox whatever= .Cells(X, whichever column matches)

    etc.  . . . 
  
    End If

    End With

    application.ScreenUpdating = False

End Sub

UPDATE the Database record after changing something in the User Form:

Private Sub cbutUPDATE_Click()


    application.ScreenUpdating = False
    
'Find last row in whatever column of database (assumes Database is sheet1)

    With Table5
    f = .Cells(.Rows.Count, "A").End(xlUp).Row

'Find whatever in Database

    Table5.Select
    .Range("A2:A" & f).Select
    
'Find whatever in Database

    Set Cell = Selection.Find(What:=tbox or cbox whatever, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

    If Cell Is Nothing Then
    Exit Sub

    Else
    
'Find whatever in the list
    
    X = Cell.Row

'Update database record
    
        .Cells(X, 1).Value = tbox or cbox whatever. value
        .Cells(X, 2).Value = tbox or cbox whatever. value
        .Cells(X, 3).Value = tbox or cbox whatever. value

         etc. . . 
        
        End If
    
    End If
    
   
    End With
    
    application.ScreenUpdating = True

End Sub

Hope this helps

Ochimus