JaxVen
05-04-2018, 06:51 AM
Hello all
I haven't been working with VBA very long and have been searching for a way to check a table within a worksheet for duplicates on saving a form.
I have found some methods and tried them, but have been unable to figure out how to check multiple.
Basically, I need to check both text boxes within the form: Name1TEXT (first name) and Name2TEXT (surname) for duplicates, which are in columns A and B within my table (NewUserTable on Sheet7). I need to prevent the same user being entered twice and unfortunately the data must have the name separated into first name and surname (would make this a lot easier if I could have one text box for full name, but I have a LOT of data being pulled from this table elsewhere that needs it to be separate). Which of course means I have to check both name fields for duplicates, there are no other identifiers. I appreciate any help anyone can offer!
Here is my current code for the form;
Private Sub Save_Click()
'Registration details
Dim LastRow As Range
Dim UserTable As ListObject
'Add row to bottom of New user table
Sheet7.ListObjects("NewUserTable").ListRows.Add
'Enter data from form into new row at bottom
Set UserTable = Sheet7.ListObjects("NewUserTable")
Set LastRow = UserTable.ListRows(UserTable.ListRows.Count).Range
With LastRow
.Cells(1, 1) = Name1TEXT.Value
.Cells(1, 2) = Name2TEXT.Value
.Cells(1, 3) = GenderCOMBO.Value
.Cells(1, 4) = PostcodeTEXT.Value
.Cells(1, 8) = Contact1TEXT.Value
.Cells(1, 9) = Contact2TEXT.Value
.Cells(1, 10) = DateRegTEXT.Value
.Cells(1, 11) = GroupCOMBO.Value
.Cells(1, 13) = EmpCOMBO.Value
.Cells(1, 19) = MedicalDetailsTEXT.Value
.Cells(1, 20) = EthnicityCOMBO.Value
.Cells(1, 21) = ReligionCOMBO
.Cells(1, 22) = SexualityCOMBO
.Cells(1, 24) = AgeCOMBO
'result for needs 1 checkbox
If PhysDysCHECK.Value = True Then
.Cells(1, 14) = "Physical Disability"
Else
.Cells(1, 14) = "None"
End If
'result for needs 2 checkbox
If LearnDiffCHECK.Value = True Then
.Cells(1, 15) = "Learning Difficulty"
Else
.Cells(1, 15) = "None"
End If
'result for needs 3 checkbox
If MentalHCHECK.Value = True Then
.Cells(1, 16) = "Mental Health"
Else
.Cells(1, 16) = "None"
End If
'result for needs 4 checkbox
If PhysHCHECK.Value = True Then
.Cells(1, 17) = "Physical Health"
Else
.Cells(1, 17) = "None"
End If
'result for allergies checkbox
If AllergiesCHECK.Value = True Then
.Cells(1, 18) = "Yes"
Else
.Cells(1, 18) = "No"
End If
End With
End Sub
I haven't been working with VBA very long and have been searching for a way to check a table within a worksheet for duplicates on saving a form.
I have found some methods and tried them, but have been unable to figure out how to check multiple.
Basically, I need to check both text boxes within the form: Name1TEXT (first name) and Name2TEXT (surname) for duplicates, which are in columns A and B within my table (NewUserTable on Sheet7). I need to prevent the same user being entered twice and unfortunately the data must have the name separated into first name and surname (would make this a lot easier if I could have one text box for full name, but I have a LOT of data being pulled from this table elsewhere that needs it to be separate). Which of course means I have to check both name fields for duplicates, there are no other identifiers. I appreciate any help anyone can offer!
Here is my current code for the form;
Private Sub Save_Click()
'Registration details
Dim LastRow As Range
Dim UserTable As ListObject
'Add row to bottom of New user table
Sheet7.ListObjects("NewUserTable").ListRows.Add
'Enter data from form into new row at bottom
Set UserTable = Sheet7.ListObjects("NewUserTable")
Set LastRow = UserTable.ListRows(UserTable.ListRows.Count).Range
With LastRow
.Cells(1, 1) = Name1TEXT.Value
.Cells(1, 2) = Name2TEXT.Value
.Cells(1, 3) = GenderCOMBO.Value
.Cells(1, 4) = PostcodeTEXT.Value
.Cells(1, 8) = Contact1TEXT.Value
.Cells(1, 9) = Contact2TEXT.Value
.Cells(1, 10) = DateRegTEXT.Value
.Cells(1, 11) = GroupCOMBO.Value
.Cells(1, 13) = EmpCOMBO.Value
.Cells(1, 19) = MedicalDetailsTEXT.Value
.Cells(1, 20) = EthnicityCOMBO.Value
.Cells(1, 21) = ReligionCOMBO
.Cells(1, 22) = SexualityCOMBO
.Cells(1, 24) = AgeCOMBO
'result for needs 1 checkbox
If PhysDysCHECK.Value = True Then
.Cells(1, 14) = "Physical Disability"
Else
.Cells(1, 14) = "None"
End If
'result for needs 2 checkbox
If LearnDiffCHECK.Value = True Then
.Cells(1, 15) = "Learning Difficulty"
Else
.Cells(1, 15) = "None"
End If
'result for needs 3 checkbox
If MentalHCHECK.Value = True Then
.Cells(1, 16) = "Mental Health"
Else
.Cells(1, 16) = "None"
End If
'result for needs 4 checkbox
If PhysHCHECK.Value = True Then
.Cells(1, 17) = "Physical Health"
Else
.Cells(1, 17) = "None"
End If
'result for allergies checkbox
If AllergiesCHECK.Value = True Then
.Cells(1, 18) = "Yes"
Else
.Cells(1, 18) = "No"
End If
End With
End Sub