Good Day
I have using this user form to add, edit, delete data. The userform thus far works brilliantly thanks to this forum and its members who assisted me.
I have this issue whereby when I am editing data, I want the system to check that the edited data is duplicated or not. The fields I am particularly concerned about are: ALB Tag# and the Power on Password and Computer Name
I have attached my spreadsheet for better clarity
Thanks
Something like:
Dim Z as range Private Sub txtTag_Change() 'Add DBN Prefix Static abort As Boolean If abort Then abort = False: Exit Sub With txtTag If Not .Text Like "DBN*" And .Text <> vbNullString Then abort = True .Text = "DBN" & .Text End If End With Set z= Columns(10).Find(txtTag.text, LookIn:=xlValues, Lookat:=xlWhole).row If Z = txtTag.Text Then MsgBox "Duplicate Entry", vbOKOnly txtTag.Clear End If End Sub
Last edited by mehmetcik; 05-01-2017 at 05:51 PM.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
HI
Tried the code and getting error on txtTag.Clear "variable not defined"
Private Sub txtTag_Change() 'Add DBN Prefix Static abort As Boolean If abort Then abort = False: Exit Sub With txtTag If Not .Text Like "DBN*" And .Text <> vbNullString Then abort = True .Text = "DBN" & .Text End If End With If Not IsError(Application.Match(txtTag.Text, Columns(10), 0)) Then MsgBox "Duplicate Entry", vbOKOnly txtTag = vbNullString End If End Sub
Last edited by bakerman2; 05-04-2017 at 01:33 AM.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
Dim Z as range Private Sub txtTag_Change() 'Add DBN Prefix Static abort As Boolean If abort Then abort = False: Exit Sub With txtTag If Not .Text Like "DBN*" And .Text <> vbNullString Then abort = True .Text = "DBN" & .Text End If End With On Error Resume Next Set z= Columns(10).Find(txtTag.text, LookIn:=xlValues, Lookat:=xlWhole) On Error Goto 0 If Z = txtTag.Text Then MsgBox "Duplicate Entry", vbOKOnly txtTag.Clear End If End Sub
Last edited by mehmetcik; 05-05-2017 at 01:06 PM.
@ mehmetcik
Code goes into debug on this line after typing first number.
Also still error on.If Z = txtTag.Text Then
as OP responded in Post#3txtTag.Clear
My Mistake
Try this instead
Private Sub txtTag_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim Z As Variant Dim t As String Static abort As Boolean If abort Then abort = False: Exit Sub With txtTag If Not .Text Like "DBN*" And .Text <> vbNullString Then abort = True .Text = "DBN" & .Text End If End With 'On Error Resume Next t = txtTag.Text Set Z = Columns("J:J").Find(t) On Error GoTo 0 If Z = txtTag.Text Then MsgBox "Duplicate Entry", vbOKOnly txtTag.Value = "" End If End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks