Infinite Loop in Error Handler

[chris]

As part of a larger program, I ask users to enter a name for the
worksheet. I want to have in built in error checking to make sure
that if the user enters a blank, just numbers, special characters, or
anything else that would cause a name in naming a worksheet that it
will ask the user for a new name until they enter one that is valid.
This is the section of code that causes problems:

Public Function CheckSheet(newSheetName As String) As String
Dim ws As Worksheet

'Makes sure that the name is a valid name in Excel (not numeric,
special characters, or blank)
On Error GoTo ErrHandler:
Set Sheets(ws).Name = newSheetName

CheckSheet = newSheetName

ErrHandler:
MsgBox "Please enter a valid name.", vbInformation
newSheetName = InputBox("What would you like to name the new
week?", "Week Name")
Resume
On Error GoTo 0
End Function

I am not that familiar with using On Error and have looked at pretty
much all the how to's on the internet, but I can't figure out my
problem. I have also tried using it a number of other ways including
a while loop that tries to make use of the IsError function, but
everything I try gives me either an error message or an infinite
loop.

I think I am getting a type mismatch error when I try to set the sheet
name to the user defined (newSheetName) variable, but I'm not sure
why.

Any help is greatly appreciated!

 

[Jim Thomlinson]

Give this a whirl...

Public Sub CheckSheet(ByVal newSheetName As String)
Dim ws As Worksheet

Set ws = Worksheets.Add
On Error Resume Next
ws.Name = newSheetName

Do While ws.Name <> newSheetName
newSheetName = InputBox("Invalid sheet name. Please try again.")
ws.Name = newSheetName
Loop
On Error GoTo 0

End Sub

 

[chris]

Thanks for the prompt reply. I think your code would have worked, but
I had to try and change it as I want to copy an existing sheet.
As is, I'm still having issues, but hopefully you will recognize the
error quickly.
I thought I would also put another related function I'm having trouble
with. The second function checks to make sure that the worksheet
name is not an existing sheet name which would also give an error. I
broke it out into a separate function because at one point I also
have to check if a sheet the user specifies exists and I was trying to
minimize code redundancy. I can do it with a while loop by scrolling
through
all the sheets, but when I have a lot of sheets, that slows it down a
lot. As a result, I was trying this shortcut, so far, without
success.

'===========================================
Public Function CheckSheet(ByVal newSheetName As String) As String
Dim ws As Worksheet

newSheetName = CheckDuplicate(newSheetName, False)

Set ws = Sheets("Template").Copy
Worksheets(ws).Move before:=Worksheets("Instructions")
On Error Resume Next
ws.Name = newSheetName

Do While ws.Name <> newSheetName
newSheetName = InputBox("Invalid sheet name. Please try
again.", "Sheet Name")
ws.Name = newSheetName
Loop
On Error GoTo 0

Sheets(newSheetName).Visible = True
CheckSheet = newSheetName

End Function

'===========================================
Public Function CheckDuplicate(newSheetName As String, Dup As Boolean)
As String
Dim Duplicate As String
Dim ws As Worksheet

Duplicate = "False"

'Checks to make sure the name entered is not already a sheet name
Do While Duplicate = "False"
Duplicate = "True"
On Error Resume Next
Set ws = Sheets(newSheetName)
On Error GoTo 0
If Not Dup Then
If Not ws Is Nothing Then
MsgBox "Sheet already exists!", vbInformation
newSheetName = InputBox("What would you like to name
the new week?", "Week Name")
Duplicate = "False"
End If
Else
If ws Is Nothing Then
MsgBox "Sheet doesn't exist!", vbInformation
newSheetName = InputBox("Please reenter the sheet
name?", "Report Generator")
Duplicate = "False"
End If
End If
Loop

CheckDuplicate = newSheetName
End Function