| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Error message needed if incorrect Date Of Birth (DOB) entered | Excel Worksheet Functions | |||
| Data error message | Excel Discussion (Misc queries) | |||
| Create error message if number entered is greater than previous ce | Excel Discussion (Misc queries) | |||
| Prevent duplicate data from being entered into the same column | Excel Worksheet Functions | |||
| How to give an error message if a cell value entered is larger than permitted | Excel Discussion (Misc queries) | |||
![RealmSteel[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
