Through VBA, I enabling the use to add another worksheet and and change it's
name. I'm hoped to use the "On Error" trapping to continue looping asking
for valide worksheet name. My problem is that the error is trapped only
once. The second time around the error no longer trapped. What's the
correct way to use "On Error" to achieve my goal? Here's the code. Thanks
- Pat

Sub AddWorkSheet()
Dim vResponse As Variant

Sheet1.Copy After:=Sheets(Sheets.Count)

On Error GoTo BadName:
BadName:
vResponse = Trim(InputBox("New worksheet name? ", , ActiveSheet.Name))
If vResponse < "" Then
ActiveSheet.Name = vResponse
End If

On Error GoTo 0
End Sub

Once an error has been encountered, error trapping is turned off until
the procedure ends (End Sub or Exit Sub) or until a Resume or Resume
Next statement is encountered. When an error is encountered, VBA
executes in "error mode" and no additional errors are trapped until a
Resume statement resets VBA out of "error mode" and back to "normal
mode". Try something like


Sub AddWorkSheet()
On Error GoTo ErrHandler
' your existing code here
ResumePoint:
Exit Sub
ErrHandler:
Resume ResumePoint
End Sub

The "Goto" when used with "On Error" doesn't work the same way as a
normal Goto.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sun, 19 Oct 2008 12:49:00 -0700, Dreiding
wrote:

Through VBA, I enabling the use to add another worksheet and and change it's
name. I'm hoped to use the "On Error" trapping to continue looping asking
for valide worksheet name. My problem is that the error is trapped only
once. The second time around the error no longer trapped. What's the
correct way to use "On Error" to achieve my goal? Here's the code. Thanks
- Pat

Sub AddWorkSheet()
Dim vResponse As Variant

Sheet1.Copy After:=Sheets(Sheets.Count)

On Error GoTo BadName:
BadName:
vResponse = Trim(InputBox("New worksheet name? ", , ActiveSheet.Name))
If vResponse < "" Then
ActiveSheet.Name = vResponse
End If

On Error GoTo 0
End Sub

Chip,

THank you. This takes care of it.
- Pat

"Chip Pearson" wrote:

Once an error has been encountered, error trapping is turned off until
the procedure ends (End Sub or Exit Sub) or until a Resume or Resume
Next statement is encountered. When an error is encountered, VBA
executes in "error mode" and no additional errors are trapped until a
Resume statement resets VBA out of "error mode" and back to "normal
mode". Try something like


Sub AddWorkSheet()
On Error GoTo ErrHandler
' your existing code here
ResumePoint:
Exit Sub
ErrHandler:
Resume ResumePoint
End Sub

The "Goto" when used with "On Error" doesn't work the same way as a
normal Goto.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sun, 19 Oct 2008 12:49:00 -0700, Dreiding
wrote:

Through VBA, I enabling the use to add another worksheet and and change it's
name. I'm hoped to use the "On Error" trapping to continue looping asking
for valide worksheet name. My problem is that the error is trapped only
once. The second time around the error no longer trapped. What's the
correct way to use "On Error" to achieve my goal? Here's the code. Thanks
- Pat

Sub AddWorkSheet()
Dim vResponse As Variant

Sheet1.Copy After:=Sheets(Sheets.Count)

On Error GoTo BadName:
BadName:
vResponse = Trim(InputBox("New worksheet name? ", , ActiveSheet.Name))
If vResponse < "" Then
ActiveSheet.Name = vResponse
End If

On Error GoTo 0
End Sub