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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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

I
  • Question Question
Receive Automation error when running code & values added to sheet twice
2
Replies
13
Views
447
NoSparks
N
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
242
krunal123
K
K
  • Question Question
Update Tables from Multi page data VBA
Replies
5
Views
1K
karolina1406
K
K
  • Question Question
Populating a table in different sheet
Replies
1
Views
307
Fluff
Fluff
E
  • Solved
Userform as search tool
Replies
5
Views
548
Akuini
Akuini

Forum statistics

Threads
1,224,830
Messages
6,181,227
Members
453,025
Latest member
Hannah_Pham93

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