spar-kle
asked on
How can I alter "On Error" code so that different code is run depending on when the error is found?
I have a workd=sheet that includes a UserForm that loads when a button is clicked.
The user can enter a code in 10 individual fields.
If the code already exists within the column of another worksheet then I would like a message appear that says that the user cannot enter a duplicate code.
I have employed a NamedRange to accommodate this that is used elsewhere in the workbook for other functions.
I thought I could use the On Error code to allow passage through the code if there were no matches with NamedRange as below, but this of course doesn't work.
How can I ammend please?
Des
On Error GoTo Stage3
If Me.TextBox101.Value = "" Then GoTo Stage3
zzz = 1
x2 = Application.WorksheetFunct ion.VLooku p((Me.Text Box101.Val ue), Sheets("Tool Database").Range("Tools"), 3, 0)
GoTo Msg1
Stage3:
On Error GoTo Stage4
If Me.TextBox106.Value = "" Then GoTo Stage4
zzz = 2
x3 = Application.WorksheetFunct ion.VLooku p((Me.Text Box106.Val ue), Sheets("Tool Database").Range("Tools"), 3, 0)
GoTo Msg1
Stage4:
...etc
The user can enter a code in 10 individual fields.
If the code already exists within the column of another worksheet then I would like a message appear that says that the user cannot enter a duplicate code.
I have employed a NamedRange to accommodate this that is used elsewhere in the workbook for other functions.
I thought I could use the On Error code to allow passage through the code if there were no matches with NamedRange as below, but this of course doesn't work.
How can I ammend please?
Des
On Error GoTo Stage3
If Me.TextBox101.Value = "" Then GoTo Stage3
zzz = 1
x2 = Application.WorksheetFunct
GoTo Msg1
Stage3:
On Error GoTo Stage4
If Me.TextBox106.Value = "" Then GoTo Stage4
zzz = 2
x3 = Application.WorksheetFunct
GoTo Msg1
Stage4:
...etc
Perhaps something like
Sub whatever()
' ....
If Me.TextBox101.Value <> "" Then
zzz = 1
x2 = LookupTool(Me.TextBox101.Value)
ElseIf Me.TextBox106.Value <> "" Then
zzz = 2
x3 = LookupTool(Me.TextBox106.Value)
' add more ElseIf statements as needed
End If
'....
End Sub
Function LookupTool(varIn) As String
Dim varVal
varVal = Application.VLookup(varIn, Sheets("Tool Database").Range("Tools"), 3, 0)
If IsError(varVal) Then
LookupTool = ""
Else
LookupTool = varVal
End If
End Function
ASKER
Thanks for your replies, really appreciated.
rorya: I can see that this will work with my workbook.
The variable zzz refers to the field number so that I can create a message for the user.
I can add code:
If IsError(varVal) Then
MsgBox ("A code entered has already been used")
How can I create a message, when the code is found within the "Tool Database" range that says, "The code entered in field " & zzz & " has already been used"
rorya: I can see that this will work with my workbook.
The variable zzz refers to the field number so that I can create a message for the user.
I can add code:
If IsError(varVal) Then
MsgBox ("A code entered has already been used")
How can I create a message, when the code is found within the "Tool Database" range that says, "The code entered in field " & zzz & " has already been used"
If zzz > 0 then msgbox "The code entered in field " & zzz & " has already been used"
if I understood you right.
ASKER
Function LookupTool(varIn) As String
Dim varVal
varVal = Application.VLookup(varIn, Sheets("Tool Database").Range("Tools"), 3, 0)
If IsError(varVal) Then
LookupTool = ""
MsgBox "The code entered in field " & zzz & " has already been used"
Else
LookupTool = varVal
End If
End Function
Sorry I meant, how do I declare the variable zzz, I presume "Public zzz As Integer" in the function module??
Also with this function I get my message "The code entered in field " & zzz & " has already been used" even when the code entered into theTextBox is not within the worksheet "Tool Database"
ie if the code IS IN the worksheet then I DO NOT get the message, if the code IS NOT within the worksheet I DO get the message ...I really need it the other way around
Actually it seems to revolve around the first TextBox
If the data in this TextBox is not present within the worksheet then I get the message, and all subsequent boxes seem to be ignored.
If the data in this TextBox is present within the worksheet then I do not get the message, and all subsequent boxes seem to be ignored.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm sorry about my ignorance, but I really appreciate your help.
This works brilliantly
Des
This works brilliantly
Des
Glad to help.
Note that wasn't a criticism (although I don't like Goto statements except in error handling) - just a statement that without the full code I couldn't see where the code was jumping to. ;)
Note that wasn't a criticism (although I don't like Goto statements except in error handling) - just a statement that without the full code I couldn't see where the code was jumping to. ;)
ASKER
...I didn't think you were criticising ...and I am truly ignorant!! :-)
but with with the help of you guys at EE I have been able to accomplish a great deal.
Really appreciate your support
but with with the help of you guys at EE I have been able to accomplish a great deal.
Really appreciate your support
Fair enough. :)
http://www.excel-examples.com/iserror.htm