VBA userform help

bayers86

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

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]

 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Forum statistics

Threads
1,223,248
Messages
6,171,011
Members
452,374
Latest member
keccles

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