I am having issues with 3 codes. Hope its okay to post all 3 together they are all part of a userform.
I created a table then a userform to enter the data., search the data, and delete data.
I am needing this code to have where it will check for duplicates and a message pops up if there is a duplicate and doesn't allow them to enter it.
I am wanting to get the combosearch to pull Fname and Lname so they can select the one they want to edit and it pull data back to userform they make changes then it changes the info on table. It works to change the ata on table but i can only get the Fname needing to get the Lname for combosearch and the search button to pull the selected.
This is the updatebutton code:
This is the search button code:
I am needing the delete button to delete the entire row of the selected. based on data they pulled from the combosearch box. Right now it clears the userform and clears the data from the row but leaves a blank row in table/
This is the Delete button code:
I created a table then a userform to enter the data., search the data, and delete data.
I am needing this code to have where it will check for duplicates and a message pops up if there is a duplicate and doesn't allow them to enter it.
Code:
[COLOR=#b22222]Private Sub Submitcmd_Click()[/COLOR]
[COLOR=#b22222] Dim oNewRow As ListRow[/COLOR]
[COLOR=#b22222] Dim rng As Range[/COLOR]
[COLOR=#b22222] Set rng = ThisWorkbook.Worksheets("List of Clients").Range("Table1")[/COLOR]
[COLOR=#b22222] rng.Select[/COLOR]
[COLOR=#b22222] Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)[/COLOR]
[COLOR=#b22222] With ws[/COLOR]
[COLOR=#b22222] oNewRow.Range.Cells(1, 1).Value = Me.Datetxt.Value[/COLOR]
[COLOR=#b22222] oNewRow.Range.Cells(1, 2).Value = Me.Fnametxt.Value[/COLOR]
[COLOR=#b22222] oNewRow.Range.Cells(1, 3).Value = Me.Lnametxt.Value[/COLOR]
[COLOR=#b22222] oNewRow.Range.Cells(1, 4).Value = Me.DOBtxt.Value
[/COLOR][COLOR=#b22222] 'Clear input controls.[/COLOR]
[COLOR=#b22222] Me.Datetxt.Value = ""[/COLOR]
[COLOR=#b22222] Me.Fnametxt.Value = ""[/COLOR]
[COLOR=#b22222] Me.Lnametxt.Value = ""[/COLOR]
[COLOR=#b22222] Me.DOBtxt.Value = ""[/COLOR]
[COLOR=#b22222] End With[/COLOR]
[COLOR=#b22222]End Sub[/COLOR][COLOR=#b22222]
[/COLOR]
I am wanting to get the combosearch to pull Fname and Lname so they can select the one they want to edit and it pull data back to userform they make changes then it changes the info on table. It works to change the ata on table but i can only get the Fname needing to get the Lname for combosearch and the search button to pull the selected.
This is the updatebutton code:
Code:
Private Sub Updatebutton_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 = Combosearch.Text Then
Sheets("List of Clients").Range("A" & row_number) = Datetxt.Text
Sheets("List of Clients").Range("B" & row_number) = Fnametxt.Text
Sheets("List of Clients").Range("C" & row_number) = Lnametxt.Text
Sheets("List of Clients").Range("D" & row_number) = DOBtxt.Text
End If
Loop Until item_in_review = ""
End Sub
This is the search button code:
Code:
Private Sub Searchbutton_Click()Dim row_number As Integer
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 = Combosearch.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)
End If
Loop Until item_in_review = ""
End Sub
I am needing the delete button to delete the entire row of the selected. based on data they pulled from the combosearch box. Right now it clears the userform and clears the data from the row but leaves a blank row in table/
This is the Delete button code:
Code:
[COLOR=#008000]Private Sub Deletecmd_Click()
[/COLOR][COLOR=#008000]'Clear input controls.[/COLOR]
[COLOR=#008000] Me.Datetxt.Value = ""[/COLOR]
[COLOR=#008000] Me.Fnametxt.Value = ""[/COLOR]
[COLOR=#008000] Me.Lnametxt.Value = ""[/COLOR]
[COLOR=#008000] Me.DOBtxt.Value = ""
[/COLOR][COLOR=#008000]row_number = 0[/COLOR]
[COLOR=#008000]Do[/COLOR]
[COLOR=#008000]DoEvents[/COLOR]
[COLOR=#008000]row_number = row_number + 1[/COLOR]
[COLOR=#008000]item_in_review = Sheets("List of Clients").Range("B" & row_number)[/COLOR]
[COLOR=#008000] If item_in_review = Combosearch.Text Then[/COLOR]
[COLOR=#008000]Sheets("List of Clients").Range("A" & row_number) = Datetxt.Text[/COLOR]
[COLOR=#008000]Sheets("List of Clients").Range("B" & row_number) = Fnametxt.Text[/COLOR]
[COLOR=#008000]Sheets("List of Clients").Range("C" & row_number) = Lnametxt.Text[/COLOR]
[COLOR=#008000]Sheets("List of Clients").Range("D" & row_number) = DOBtxt.Text
[/COLOR][COLOR=#008000]End If[/COLOR]
[COLOR=#008000]Loop Until item_in_review = ""[/COLOR]
[COLOR=#008000]End Sub[/COLOR]