sheexin
07-01-2015, 09:13 PM
Hello, I have a user form that should reject user input with a message box if there is a duplicate catalogue ISBN number entry. My code is as such:
Private Sub cmdSave_Click()
Dim ColISBN As Range
If txtCataTitle.Value = "" Then
txtCataTitle.BackColor = vbRed
lblCataTitle.ForeColor = vbRed
txtCataTitle.SetFocus
Exit Sub
End If
If txtArtist.Value = "" Then
txtArtist.BackColor = vbRed
lblArtist.ForeColor = vbRed
txtArtist.SetFocus
Exit Sub
End If
If txtNum.BackColor = vbRed Then
Exit Sub
End If
If txtCataDesc.Value = "" Then
txtCataDesc.BackColor = vbRed
lblCataDesc.ForeColor = vbRed
txtCataDesc.SetFocus
Exit Sub
End If
If txtISBN.Value = "" Then
txtISBN.BackColor = vbRed
lblISBN.ForeColor = vbRed
txtISBN.SetFocus
Exit Sub
End If
'column to search ISBN numbers in for duplicates
Set ColISBN = Range("F2", Range("F2").End(xlDown)).Find(what:=txtISBN, LookIn:=xlValues, lookat:=xlWhole)
If ColISBN = True Then
MsgBox ("Duplicate catalogue exists.")
Exit Sub
Else
'first row is empty
If Range("A2") = "" Then
Range("A2").Value = 1
Range("A2").Select
ActiveCell.Offset(0, 1).Value = txtCataTitle
ActiveCell.Offset(0, 2).Value = txtArtist
ActiveCell.Offset(0, 3).Value = txtCataDesc
ActiveCell.Offset(0, 4).Value = txtNum
ActiveCell.Offset(0, 5).Value = txtISBN
Else
'first row not empty
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1
ActiveCell.Offset(0, 1).Value = txtCataTitle
ActiveCell.Offset(0, 2).Value = txtArtist
ActiveCell.Offset(0, 3).Value = txtCataDesc
ActiveCell.Offset(0, 4).Value = txtNum
ActiveCell.Offset(0, 5).Value = txtISBN
End If
Unload Me
End If
End Sub
Previously, I did not add in dim ColISBN nor
'column to search ISBN numbers in for duplicates
Set ColISBN = Range("F2", Range("F2").End(xlDown)).Find(what:=txtISBN, LookIn:=xlValues, lookat:=xlWhole)
If ColISBN = True Then
MsgBox ("Duplicate catalogue exists.")
Exit Sub
and the user form was able to add in new catalogues. However, to prevent duplicates by adding the code above, I was able to successfully detect duplicates, but now whenever I want to add a new unique entry, If ColISBN = True Then is highlighted and it shows object variable or with block variable not set. Any advice? :-)
Private Sub cmdSave_Click()
Dim ColISBN As Range
If txtCataTitle.Value = "" Then
txtCataTitle.BackColor = vbRed
lblCataTitle.ForeColor = vbRed
txtCataTitle.SetFocus
Exit Sub
End If
If txtArtist.Value = "" Then
txtArtist.BackColor = vbRed
lblArtist.ForeColor = vbRed
txtArtist.SetFocus
Exit Sub
End If
If txtNum.BackColor = vbRed Then
Exit Sub
End If
If txtCataDesc.Value = "" Then
txtCataDesc.BackColor = vbRed
lblCataDesc.ForeColor = vbRed
txtCataDesc.SetFocus
Exit Sub
End If
If txtISBN.Value = "" Then
txtISBN.BackColor = vbRed
lblISBN.ForeColor = vbRed
txtISBN.SetFocus
Exit Sub
End If
'column to search ISBN numbers in for duplicates
Set ColISBN = Range("F2", Range("F2").End(xlDown)).Find(what:=txtISBN, LookIn:=xlValues, lookat:=xlWhole)
If ColISBN = True Then
MsgBox ("Duplicate catalogue exists.")
Exit Sub
Else
'first row is empty
If Range("A2") = "" Then
Range("A2").Value = 1
Range("A2").Select
ActiveCell.Offset(0, 1).Value = txtCataTitle
ActiveCell.Offset(0, 2).Value = txtArtist
ActiveCell.Offset(0, 3).Value = txtCataDesc
ActiveCell.Offset(0, 4).Value = txtNum
ActiveCell.Offset(0, 5).Value = txtISBN
Else
'first row not empty
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1
ActiveCell.Offset(0, 1).Value = txtCataTitle
ActiveCell.Offset(0, 2).Value = txtArtist
ActiveCell.Offset(0, 3).Value = txtCataDesc
ActiveCell.Offset(0, 4).Value = txtNum
ActiveCell.Offset(0, 5).Value = txtISBN
End If
Unload Me
End If
End Sub
Previously, I did not add in dim ColISBN nor
'column to search ISBN numbers in for duplicates
Set ColISBN = Range("F2", Range("F2").End(xlDown)).Find(what:=txtISBN, LookIn:=xlValues, lookat:=xlWhole)
If ColISBN = True Then
MsgBox ("Duplicate catalogue exists.")
Exit Sub
and the user form was able to add in new catalogues. However, to prevent duplicates by adding the code above, I was able to successfully detect duplicates, but now whenever I want to add a new unique entry, If ColISBN = True Then is highlighted and it shows object variable or with block variable not set. Any advice? :-)