Error message if duplicate data entered
 

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.

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.

Error message if duplicate data entered
 

You were right. I didn't think about that.

This is all I had to do:

If txtRFINum.Text = ActiveCell.Value Or txtRFINum.Text =
ActiveCell.Text Then
MsgBox "RFI Number Exists! Try Different Number."
GoTo DupRFI

Thanks a ton.

Rich

Zone wrote:
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

Error message if duplicate data entered
 

Realm, You are most welcome. As usual, I have learned something from
answering a post. Your approach to the problem is interesting. I've
never used the .Text property with either a textbox or a cell. This
property seems to be interpreting the two variables as text, regardless
of what they are, so I'll file that away for future reference.
Tally-ho! James
RealmSteel wrote:
You were right. I didn't think about that.

This is all I had to do:

If txtRFINum.Text = ActiveCell.Value Or txtRFINum.Text =
ActiveCell.Text Then
MsgBox "RFI Number Exists! Try Different Number."
GoTo DupRFI

Thanks a ton.

Rich

Zone wrote:
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

Error message if duplicate data entered
 

well, maybe with a textbox...heh J
Zone wrote:
Realm, You are most welcome. As usual, I have learned something from
answering a post. Your approach to the problem is interesting. I've
never used the .Text property with either a textbox or a cell. This
property seems to be interpreting the two variables as text, regardless
of what they are, so I'll file that away for future reference.
Tally-ho! James
RealmSteel wrote:
You were right. I didn't think about that.

This is all I had to do:

If txtRFINum.Text = ActiveCell.Value Or txtRFINum.Text =
ActiveCell.Text Then
MsgBox "RFI Number Exists! Try Different Number."
GoTo DupRFI

Thanks a ton.

Rich

Zone wrote:
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