Good day, I have the following code in a userform, which worked fine until i tried to put in a yes no function.
I need the code to run and check if the are any duplicate names, which it does, if a no duplicate names are found then add the data.
However if it finds a duplicate name then it should ask the question - there is a person with the same name continue yes or no.
if yes then it adds 1 behind the name, if no then it should exit.
I'm sure i done it half right, but thats not good enough, i think i may have put in the end if's in the incorrect place.
here is the current code, many thx
I need the code to run and check if the are any duplicate names, which it does, if a no duplicate names are found then add the data.
However if it finds a duplicate name then it should ask the question - there is a person with the same name continue yes or no.
if yes then it adds 1 behind the name, if no then it should exit.
I'm sure i done it half right, but thats not good enough, i think i may have put in the end if's in the incorrect place.
here is the current code, many thx
Code:
Private Sub cmb_submit_Click()
Dim LastRow As Long
Dim LR As Integer
Dim ws As Worksheet
Dim n As Range
Dim MSG1 As Long
If tb_surname.Value = "" Then
Unload Me
Sheets("DASHBOARD").Activate
Exit Sub
End If
Set n = [a:a].Find(tb_surname.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not n Is Nothing And tb_surname <> "" Then
''End If
'Exit Sub
tb_surname.Value = tb_surname & 1
If tb_surname.Value = tb_surname & 1 Then
If tb_surname.Value = tb_surname Then
End If
'Else
' End If
MSG1 = MsgBox("THERE IS A PATIENT WITH THE SAME NAME, DO YOU WANT TO CONTUNE", vbYesNo)
'End If
If MSG1 = vbYes Then
' Sheets("DASHBOARD").Activate
'Unload Me
' If MSG1 = vbNo Then
'End If
'Exit Sub
'End If
End If
' Else
End If
' End If
LastRow = Sheets("PATIENT DATA").Range("a" & Rows.Count).End(xlUp).Row
Cells(LastRow + 1, "a").Value = tb_surname.Text
Cells(LastRow + 1, "b").Value = tb_name.Text
Cells(LastRow + 1, "c").Value = tb_id.Text
Cells(LastRow + 1, "d").Value = tb_street.Text
Cells(LastRow + 1, "e").Value = tb_suburb.Text
Cells(LastRow + 1, "f").Value = cb_towns.Text
Cells(LastRow + 1, "g").Value = cb_country.Value
Cells(LastRow + 1, "h").Value = tb_email.Text
Cells(LastRow + 1, "i").Value = tb_home.Text
Cells(LastRow + 1, "j").Value = tb_work.Text
Cells(LastRow + 1, "k").Value = tb_cell.Text
Cells(LastRow + 1, "l").Value = tb_refby.Text
Cells(LastRow + 1, "m").Value = cb_refbypat.Value
Cells(LastRow + 1, "n").Value = tb_mainsurname.Text
Cells(LastRow + 1, "o").Value = tb_mainname.Text
Cells(LastRow + 1, "p").Value = tb_mainid.Text
Cells(LastRow + 1, "q").Value = tb_scheme.Text
Cells(LastRow + 1, "r").Value = tb_option.Text
Cells(LastRow + 1, "s").Value = tb_medno.Text
Cells(LastRow + 1, "t").Value = tb_maincode.Text
Cells(LastRow + 1, "u").Value = tb_patcode.Text
Cells(LastRow + 1, "w").Value = tb_date.Text
Cells(LastRow + 1, "x").Value = tb_lastv.Text
'Cells(lastrow + 1, "ar").Value = tb_before.Text
Cells(LastRow + 1, "aq").Value = tb_medyn.Text
LR = Range("A" & Rows.Count).End(xlUp).Row
Application.EnableEvents = False
Range("A3:BI" & LR).Sort Key1:=Range("A3"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.EnableEvents = True
ActiveWorkbook.Save
Unload newpatient
Sheets("DASHBOARD").Activate
If MSG1 = vbNo Then
Unload Me
Sheets("DASHBOARD").Activate
Exit Sub
End If
'End If
End Sub[code]
