error handler not working correctly

[Mike K]

Oh wise ones,
From the existing code below I crudely added some
error code from right out of the help section. The form would crap out to
debug mode if a valid red tag number was not entered. I just wanted it to
display a message and start the sub again. Now is displays a message but goes
to the next form and the data is put in the wrong cells. I would be shocked
if you guys took more the 3 seconds to fix this one. Please advise.

Thanks,
Mike

Sub Sup()
Dim Message, Title, Default, Squares
Dim closetag As Integer
On Error GoTo ErrorHandler<< I added this
Message = "Enter the red tag number that was reworked" ' Set
prompt.
Title = "Red Tag Number" ' Set title.
Default = "0" ' Set default.
closetag = InputBox(Message, Title, Default)
Worksheets("Closed Red Tags").Rows(4).Insert
Worksheets("Open Red Tags").Activate
With Worksheets("Open Red Tags").Range("a4:a500")
Set C = .Find(closetag, LookIn:=xlValues, LookAt:=xlWhole,
MatchByte:=True)
C.Select
Selection.Range("a1:k1").Cut
Worksheets("Closed Red Tags").Activate
Range("A4").Select
ActiveSheet.Paste
Worksheets("Closed Red Tags").Range("N4") = Date
Worksheets("Open Red Tags").Activate
Supervisor.Show
End With


Exit Sub << I added this
ErrorHandler: << I added this
MsgBox "Please enter a valid Redtag ID" << I added this
Resume Next << I added this

End Sub

 

[Chris]

Hey Mike,

If i understand you right, you would like to begin at the beginning of
the sub after the Error handler.
To do this you have to make a rule name (just like "ErrorHandler:") at
the beginnig of the sub.
secondly you have to make an adjustment to your messagebox so he know
what to do after the message.

If MsgBox("Please enter a valid Redtag ID", vbOKOnly) = vbOK Then GoTo
beginning

And i dont know if you have to use "Exit Sub" i never do, and it works
perfectly.

Greets Chris

 

[Rich_z]

Well Mike,

It took me more than three seconds to figure out what your code wa
doing so does my answer qualify ??

Firstly, error handlers need some sort of intelligence, so the way
write mine are like so:


Code
-------------------

Dim Found As Boolean
'*
On Local Error GoTo as_Err
...
...
...
as_Res:
Worksheets(Sheet_Name).Activate
On Local Error GoTo 0
Exit Sub
as_Err:
Select Case Err.Number
Case 9 '* Worksheet not found
Call Create_Sheet(Sheet_Name)
Resume as_Res
Case Else
On Local Error GoTo 0
Resume
End Select
End Sub

-------------------


So, the first thing to do is to use a -Local- error handler. If yo
don't do this then the error handler adds itself onto the chain o
previously declared error handlers and errors rattle up and down th
chain causing some difficult bugs in some cases.

The second thing to do is to turn your error handler off at the end o
the code otherwise any other errors elsewhere will use this erro
handler which is something you don't want.

Thirdly, give your error handler some intelligence. You don't want i
to react the same way for every error. Also give it a way to react t
an unexpected error. The 'Case Else' statement above is my 'give up
clause.

Every handler must be accompanied by a 'resume' statement. Resum
statements come in three flavours:


- Resume
- Resume Next
- Resume [Label]


Resume Resumes(!!) execution at the statement that caused the error.
Resume Next resumes at the statement after the error

Resume [label] (Such as the resume as_Res statement above) resumes at
particular statement.

What you need to do is to place a label (New_tag) like:


Code
-------------------

New_tag:
Message = "Enter the red tag number that was reworked" ' Set
prompt.
Title = "Red Tag Number" ' Set title.
Default = "0" ' Set default.
closetag = InputBox(Message, Title, Default)

-------------------


Code your error handler as I have shown above and then include th
statement


Code
-------------------

Resume New_tag

-------------------


instead of the

Code
-------------------


Resume Next


-------------------


Regards

Ric