VBA search, edit and delete commandbutton on userform

B

bayers86

New Member
Joined
Oct 11, 2017
Messages
3
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
okay so i have i have figured out some of it on my own now just need to add some stuff. Maybe i can get some help. This is my vba code for the search button i have left out some of the txtboxes to shorten so it would be a long post. The issue i am having is i want it to list all matches in a listbox where they can double click the one that want to edit.

Private Sub Searchbutton_Click()
row_number = 0
Do
DoEvents
row_number = row_number + 1
item_in_review = Sheets("List of Clients").Range("B" & row_number)
If item_in_review = Searchbox.Text Then
Datetxt.Text = Sheets("List of Clients").Range("A" & row_number)
Fnametxt.Text = Sheets("List of Clients").Range("B" & row_number)
Lnametxt.Text = Sheets("List of Clients").Range("C" & row_number)
DOBtxt.Text = Sheets("List of Clients").Range("D" & row_number)
Vetcombo.Text = Sheets("List of Clients").Range("E" & row_number)
End If
Loop Until item_in_review = ""
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Update Tables from Multi page data VBA
Replies
5
Views
1K
karolina1406
K
K
  • Question
Populating a table in different sheet
Replies
1
Views
233
Fluff
Fluff
E
  • Solved
Userform as search tool
Replies
5
Views
260
Akuini
Akuini
P
  • Question
Userform Listbox Edit Rows Within Sheet
Replies
1
Views
436
pure vito
P
C
  • Question
Excel Userform Listbox Search issues
Replies
3
Views
125
Akuini
Akuini

Forum statistics

Threads
1,215,003
Messages
6,122,655
Members
449,091
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top