I have created a userform that inputs data into a table, the code below works for adding the data. the trouble i am having is getting the search command button to work to look up Fnametxt in Col B of table and pull data back to userform based on name type into search box. then to beable to update the data in the userform and it make the changes to the table, or to just beable to delete the data completely.
The issue with below is i get an error on rng.select when I launch userform from interface sheet. the table is on sheet 2 (List of Clients) sheet 1 is set up with just command button and image to launch userform.
I skipped col 17 for a reason.
Private Sub Submitcmd_Click()
Dim oNewRow As ListRow
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("List of Clients").Range("Table1")
rng.Select
Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
With ws
oNewRow.Range.Cells(1, 1).Value = Me.Datetxt.Value
oNewRow.Range.Cells(1, 2).Value = Me.Fnametxt.Value
oNewRow.Range.Cells(1, 3).Value = Me.Lnametxt.Value
oNewRow.Range.Cells(1, 4).Value = Me.DOBtxt.Value
oNewRow.Range.Cells(1, 5).Value = Me.Vetcombo.Value
oNewRow.Range.Cells(1, 6).Value = Me.Activecombo.Value
oNewRow.Range.Cells(1, 7).Value = Me.Gendercombo.Value
oNewRow.Range.Cells(1, 8).Value = Me.Paddresstxt.Value
oNewRow.Range.Cells(1, 9).Value = Me.Citytxt.Value
oNewRow.Range.Cells(1, 10).Value = Me.Statetxt.Value
oNewRow.Range.Cells(1, 11).Value = Me.Ziptxt.Value
oNewRow.Range.Cells(1, 12).Value = Me.Maddresstxt.Value
oNewRow.Range.Cells(1, 13).Value = Me.Dphonetxt.Value
oNewRow.Range.Cells(1, 14).Value = Me.Cphonetxt.Value
oNewRow.Range.Cells(1, 15).Value = Me.Econtacttxt.Value
oNewRow.Range.Cells(1, 16).Value = Me.Econtactnumbertxt.Value
oNewRow.Range.Cells(1, 18).Value = Me.Salarytxt.Value
oNewRow.Range.Cells(1, 19).Value = Me.Welfaretxt.Value
oNewRow.Range.Cells(1, 20).Value = Me.Csupporttxt.Value
oNewRow.Range.Cells(1, 21).Value = Me.SOCtxt.Value
oNewRow.Range.Cells(1, 22).Value = Me.VAtxt.Value
oNewRow.Range.Cells(1, 23).Value = Me.Unemploymenttxt.Value
oNewRow.Range.Cells(1, 24).Value = Me.Retirementtxt.Value
oNewRow.Range.Cells(1, 25).Value = Me.Deptxt.Value
oNewRow.Range.Cells(1, 26).Value = Me.Adeptxt.Value
oNewRow.Range.Cells(1, 27).Value = Me.A2deptxt.Value
oNewRow.Range.Cells(1, 28).Value = Me.Cdeptxt.Value
oNewRow.Range.Cells(1, 29).Value = Me.Name1txt.Value
oNewRow.Range.Cells(1, 30).Value = Me.Age1txt.Value
oNewRow.Range.Cells(1, 31).Value = Me.Relation1txt.Value
oNewRow.Range.Cells(1, 32).Value = Me.Name2txt.Value
oNewRow.Range.Cells(1, 33).Value = Me.Age2txt.Value
oNewRow.Range.Cells(1, 34).Value = Me.Relation2txt.Value
oNewRow.Range.Cells(1, 35).Value = Me.Name3txt.Value
oNewRow.Range.Cells(1, 36).Value = Me.Age3txt.Value
oNewRow.Range.Cells(1, 37).Value = Me.Relation3txt.Value
oNewRow.Range.Cells(1, 38).Value = Me.Name4txt.Value
oNewRow.Range.Cells(1, 39).Value = Me.Age4txt.Value
oNewRow.Range.Cells(1, 40).Value = Me.Relation4txt.Value
oNewRow.Range.Cells(1, 41).Value = Me.Name5txt.Value
oNewRow.Range.Cells(1, 42).Value = Me.Age5txt.Value
oNewRow.Range.Cells(1, 43).Value = Me.Relation5txt.Value
oNewRow.Range.Cells(1, 44).Value = Me.Name6txt.Value
oNewRow.Range.Cells(1, 45).Value = Me.Age6txt.Value
oNewRow.Range.Cells(1, 46).Value = Me.Relation6txt.Value
oNewRow.Range.Cells(1, 47).Value = Me.Name7txt.Value
oNewRow.Range.Cells(1, 48).Value = Me.Age7txt.Value
oNewRow.Range.Cells(1, 49).Value = Me.Relation7txt.Value
oNewRow.Range.Cells(1, 50).Value = Me.Name8txt.Value
oNewRow.Range.Cells(1, 51).Value = Me.Age8txt.Value
oNewRow.Range.Cells(1, 52).Value = Me.Relation8txt.Value
oNewRow.Range.Cells(1, 53).Value = Me.Name9txt.Value
oNewRow.Range.Cells(1, 54).Value = Me.Age9txt.Value
oNewRow.Range.Cells(1, 55).Value = Me.Relation9txt.Value
oNewRow.Range.Cells(1, 56).Value = Me.Name10txt.Value
oNewRow.Range.Cells(1, 57).Value = Me.Age10txt.Value
oNewRow.Range.Cells(1, 58).Value = Me.Relation10txt.Value
oNewRow.Range.Cells(1, 59).Value = Me.IDcombo.Value
oNewRow.Range.Cells(1, 60).Value = Me.Presidencycombo.Value
oNewRow.Range.Cells(1, 61).Value = Me.pInccombo.Value
oNewRow.Range.Cells(1, 62).Value = Me.Drivercombo.Value
oNewRow.Range.Cells(1, 63).Value = Me.SOCcombo.Value
oNewRow.Range.Cells(1, 64).Value = Me.Dateoffoodtxt.Value
'Clear input controls.
Me.Datetxt.Value = ""
Me.Fnametxt.Value = ""
Me.Lnametxt.Value = ""
Me.DOBtxt.Value = ""
Me.Vetcombo.Value = ""
Me.Activecombo.Value = ""
Me.Gendercombo.Value = ""
Me.Paddresstxt.Value = ""
Me.Citytxt.Value = ""
Me.Statetxt.Value = ""
Me.Ziptxt.Value = ""
Me.Maddresstxt.Value = ""
Me.Dphonetxt.Value = ""
Me.Cphonetxt.Value = ""
Me.Econtacttxt.Value = ""
Me.Econtactnumbertxt.Value = ""
Me.Salarytxt.Value = ""
Me.Welfaretxt.Value = ""
Me.Csupporttxt.Value = ""
Me.SOCtxt.Value = ""
Me.VAtxt.Value = ""
Me.Unemploymenttxt.Value = ""
Me.Retirementtxt.Value = ""
Me.Deptxt.Value = ""
Me.Adeptxt.Value = ""
Me.A2deptxt.Value = ""
Me.Cdeptxt.Value = ""
Me.Name1txt.Value = ""
Me.Age1txt.Value = ""
Me.Relation1txt.Value = ""
Me.Name2txt.Value = ""
Me.Age2txt.Value = ""
Me.Relation2txt.Value = ""
Me.Name3txt.Value = ""
Me.Age3txt.Value = ""
Me.Relation3txt.Value = ""
Me.Name4txt.Value = ""
Me.Age4txt.Value = ""
Me.Relation4txt.Value = ""
Me.Name5txt.Value = ""
Me.Age5txt.Value = ""
Me.Relation5txt.Value = ""
Me.Name6txt.Value = ""
Me.Age6txt.Value = ""
Me.Relation6txt.Value = ""
Me.Name7txt.Value = ""
Me.Age7txt.Value = ""
Me.Relation7txt.Value = ""
Me.Name8txt.Value = ""
Me.Age8txt.Value = ""
Me.Relation8txt.Value = ""
Me.Name9txt.Value = ""
Me.Age9txt.Value = ""
Me.Relation9txt.Value = ""
Me.Name10txt.Value = ""
Me.Age10txt.Value = ""
Me.Relation10txt.Value = ""
Me.IDcombo.Value = ""
Me.Presidencycombo.Value = ""
Me.pInccombo.Value = ""
Me.Drivercombo.Value = ""
Me.SOCcombo.Value = ""
Me.Dateoffoodtxt.Value = ""
End With
End Sub
The issue with below is i get an error on rng.select when I launch userform from interface sheet. the table is on sheet 2 (List of Clients) sheet 1 is set up with just command button and image to launch userform.
I skipped col 17 for a reason.
Private Sub Submitcmd_Click()
Dim oNewRow As ListRow
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("List of Clients").Range("Table1")
rng.Select
Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
With ws
oNewRow.Range.Cells(1, 1).Value = Me.Datetxt.Value
oNewRow.Range.Cells(1, 2).Value = Me.Fnametxt.Value
oNewRow.Range.Cells(1, 3).Value = Me.Lnametxt.Value
oNewRow.Range.Cells(1, 4).Value = Me.DOBtxt.Value
oNewRow.Range.Cells(1, 5).Value = Me.Vetcombo.Value
oNewRow.Range.Cells(1, 6).Value = Me.Activecombo.Value
oNewRow.Range.Cells(1, 7).Value = Me.Gendercombo.Value
oNewRow.Range.Cells(1, 8).Value = Me.Paddresstxt.Value
oNewRow.Range.Cells(1, 9).Value = Me.Citytxt.Value
oNewRow.Range.Cells(1, 10).Value = Me.Statetxt.Value
oNewRow.Range.Cells(1, 11).Value = Me.Ziptxt.Value
oNewRow.Range.Cells(1, 12).Value = Me.Maddresstxt.Value
oNewRow.Range.Cells(1, 13).Value = Me.Dphonetxt.Value
oNewRow.Range.Cells(1, 14).Value = Me.Cphonetxt.Value
oNewRow.Range.Cells(1, 15).Value = Me.Econtacttxt.Value
oNewRow.Range.Cells(1, 16).Value = Me.Econtactnumbertxt.Value
oNewRow.Range.Cells(1, 18).Value = Me.Salarytxt.Value
oNewRow.Range.Cells(1, 19).Value = Me.Welfaretxt.Value
oNewRow.Range.Cells(1, 20).Value = Me.Csupporttxt.Value
oNewRow.Range.Cells(1, 21).Value = Me.SOCtxt.Value
oNewRow.Range.Cells(1, 22).Value = Me.VAtxt.Value
oNewRow.Range.Cells(1, 23).Value = Me.Unemploymenttxt.Value
oNewRow.Range.Cells(1, 24).Value = Me.Retirementtxt.Value
oNewRow.Range.Cells(1, 25).Value = Me.Deptxt.Value
oNewRow.Range.Cells(1, 26).Value = Me.Adeptxt.Value
oNewRow.Range.Cells(1, 27).Value = Me.A2deptxt.Value
oNewRow.Range.Cells(1, 28).Value = Me.Cdeptxt.Value
oNewRow.Range.Cells(1, 29).Value = Me.Name1txt.Value
oNewRow.Range.Cells(1, 30).Value = Me.Age1txt.Value
oNewRow.Range.Cells(1, 31).Value = Me.Relation1txt.Value
oNewRow.Range.Cells(1, 32).Value = Me.Name2txt.Value
oNewRow.Range.Cells(1, 33).Value = Me.Age2txt.Value
oNewRow.Range.Cells(1, 34).Value = Me.Relation2txt.Value
oNewRow.Range.Cells(1, 35).Value = Me.Name3txt.Value
oNewRow.Range.Cells(1, 36).Value = Me.Age3txt.Value
oNewRow.Range.Cells(1, 37).Value = Me.Relation3txt.Value
oNewRow.Range.Cells(1, 38).Value = Me.Name4txt.Value
oNewRow.Range.Cells(1, 39).Value = Me.Age4txt.Value
oNewRow.Range.Cells(1, 40).Value = Me.Relation4txt.Value
oNewRow.Range.Cells(1, 41).Value = Me.Name5txt.Value
oNewRow.Range.Cells(1, 42).Value = Me.Age5txt.Value
oNewRow.Range.Cells(1, 43).Value = Me.Relation5txt.Value
oNewRow.Range.Cells(1, 44).Value = Me.Name6txt.Value
oNewRow.Range.Cells(1, 45).Value = Me.Age6txt.Value
oNewRow.Range.Cells(1, 46).Value = Me.Relation6txt.Value
oNewRow.Range.Cells(1, 47).Value = Me.Name7txt.Value
oNewRow.Range.Cells(1, 48).Value = Me.Age7txt.Value
oNewRow.Range.Cells(1, 49).Value = Me.Relation7txt.Value
oNewRow.Range.Cells(1, 50).Value = Me.Name8txt.Value
oNewRow.Range.Cells(1, 51).Value = Me.Age8txt.Value
oNewRow.Range.Cells(1, 52).Value = Me.Relation8txt.Value
oNewRow.Range.Cells(1, 53).Value = Me.Name9txt.Value
oNewRow.Range.Cells(1, 54).Value = Me.Age9txt.Value
oNewRow.Range.Cells(1, 55).Value = Me.Relation9txt.Value
oNewRow.Range.Cells(1, 56).Value = Me.Name10txt.Value
oNewRow.Range.Cells(1, 57).Value = Me.Age10txt.Value
oNewRow.Range.Cells(1, 58).Value = Me.Relation10txt.Value
oNewRow.Range.Cells(1, 59).Value = Me.IDcombo.Value
oNewRow.Range.Cells(1, 60).Value = Me.Presidencycombo.Value
oNewRow.Range.Cells(1, 61).Value = Me.pInccombo.Value
oNewRow.Range.Cells(1, 62).Value = Me.Drivercombo.Value
oNewRow.Range.Cells(1, 63).Value = Me.SOCcombo.Value
oNewRow.Range.Cells(1, 64).Value = Me.Dateoffoodtxt.Value
'Clear input controls.
Me.Datetxt.Value = ""
Me.Fnametxt.Value = ""
Me.Lnametxt.Value = ""
Me.DOBtxt.Value = ""
Me.Vetcombo.Value = ""
Me.Activecombo.Value = ""
Me.Gendercombo.Value = ""
Me.Paddresstxt.Value = ""
Me.Citytxt.Value = ""
Me.Statetxt.Value = ""
Me.Ziptxt.Value = ""
Me.Maddresstxt.Value = ""
Me.Dphonetxt.Value = ""
Me.Cphonetxt.Value = ""
Me.Econtacttxt.Value = ""
Me.Econtactnumbertxt.Value = ""
Me.Salarytxt.Value = ""
Me.Welfaretxt.Value = ""
Me.Csupporttxt.Value = ""
Me.SOCtxt.Value = ""
Me.VAtxt.Value = ""
Me.Unemploymenttxt.Value = ""
Me.Retirementtxt.Value = ""
Me.Deptxt.Value = ""
Me.Adeptxt.Value = ""
Me.A2deptxt.Value = ""
Me.Cdeptxt.Value = ""
Me.Name1txt.Value = ""
Me.Age1txt.Value = ""
Me.Relation1txt.Value = ""
Me.Name2txt.Value = ""
Me.Age2txt.Value = ""
Me.Relation2txt.Value = ""
Me.Name3txt.Value = ""
Me.Age3txt.Value = ""
Me.Relation3txt.Value = ""
Me.Name4txt.Value = ""
Me.Age4txt.Value = ""
Me.Relation4txt.Value = ""
Me.Name5txt.Value = ""
Me.Age5txt.Value = ""
Me.Relation5txt.Value = ""
Me.Name6txt.Value = ""
Me.Age6txt.Value = ""
Me.Relation6txt.Value = ""
Me.Name7txt.Value = ""
Me.Age7txt.Value = ""
Me.Relation7txt.Value = ""
Me.Name8txt.Value = ""
Me.Age8txt.Value = ""
Me.Relation8txt.Value = ""
Me.Name9txt.Value = ""
Me.Age9txt.Value = ""
Me.Relation9txt.Value = ""
Me.Name10txt.Value = ""
Me.Age10txt.Value = ""
Me.Relation10txt.Value = ""
Me.IDcombo.Value = ""
Me.Presidencycombo.Value = ""
Me.pInccombo.Value = ""
Me.Drivercombo.Value = ""
Me.SOCcombo.Value = ""
Me.Dateoffoodtxt.Value = ""
End With
End Sub