Error message if duplicate data entered
Realm,
Just a guess. Textbox values are text, so comparing a textbox value to
a number isn't going to work. How about this:
If Val(txtRFINum.Value) = ActiveCell.Value Then
Not tested! HTH, James
RealmSteel wrote:
I have the following code for when the user clicks "submit" in a user
form.
The variable txtRFINum has be a unique number. If not, I need an error
message and to exit out of this routine. The first empty cell in column
A is filled with txtRFINum if it is unique.
I don't close the user form until this routine finishes, so my thought
was if I just pop the message up, they click OK and correct the error.
My thought was to do the comparison of the txtRFINum as it was
searching for the first empty cell.
This is what I am starting with: This is a pretty large snip, of the
complete code.
Private Sub cmdSubmit_Click()
Application.ScreenUpdating = False
Worksheets("RFI LOG").Unprotect
ActiveWorkbook.Sheets("RFI LOG").Activate
Range("A12").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
If txtRFINum.Value = "" Then
txtRFINum.Value = "None"
End If
strDocName = "RFI-" & CStr(txtRFINum.Value)
ActiveCell.Value = txtRFINum.Value
If txtDate.Value = "" Then
txtDate.Value = Date
End If
ActiveCell.Offset(0, 1) = txtDate.Value
ActiveCell.Offset(0, 3) = txtCustRFI.Value
ActiveCell.Offset(0, 5) = txtSubject.Value
' Sheets("Template").Copy After:=Worksheets(Worksheets.Count)
'Test if no RFI number
If txtRFINum.Value = "None" Then GoTo NORFI
Sheets("Template").Visible = True
Sheets("Template").Select
Sheets("Template").Copy After:=Worksheets(Worksheets.Count)
Sheets("Template (2)").Select
Sheets("Template (2)").Name = strDocName
Range("F8:G8") = txtRFINum.Value
Range("J8:L8") = txtDate.Value
Range("B13:L13") = txtSubject.Value
If txtRespondBy.Value = "" Then
txtRespondBy.Value = Date + 7
End If
Range("D14:H14") = txtRespondBy.Value
Range("I11:L11") = txtSubmitVia.Value
If optCritical = True Then
Range("K14:L14") = "Critical"
ElseIf optHigh = True Then
Range("K14:L14") = "High"
Else
Range("K14:L14") = "Normal"
End If
NewSht = "'" & strDocName & "'!A1"
ActiveWorkbook.Sheets("RFI LOG").Activate
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell.Offset(0, 2), _
Address:="", SubAddress:=NewSht, TextToDisplay:=strDocName
Sheets("Template").Select
ActiveWindow.SelectedSheets.Visible = False
' FormatCells Macro This portion formats a series of cells.
' It will skip to here if No RFI number entered
NORFI:
'snip
'snip
' End of FormatCells Macro
Worksheets("RFI LOG").Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
Unload Me
End Sub
Here is where I am at right now and this doesn't work properly.
At this point, it is not recognizing the values are the same so it
continues on
Range("A12").Select
Do
'Tests for Duplicate RFI
If txtRFINum.Value = ActiveCell.Value Then -
------------------------This appears to be where the problem is
MsgBox "RFI Number Exists! Try Different Number."
GoTo DupRFI
End If
'If current cell is not same as RFI then move along
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
If txtRFINum.Value = "" Then
txtRFINum.Value = "None"
End If
strDocName = "RFI-" & CStr(txtRFINum.Value)
'snip
'snip
Unload Me
DupRFI:
End Sub
Any ideas would be appreciated.
|