LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default Infinite Loop in Error Handler

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!

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Error Handler sharad Excel Discussion (Misc queries) 1 September 17th 07 06:38 PM
URGENT- Can't get out of infinite loop J@Y Excel Discussion (Misc queries) 4 June 14th 07 09:41 PM
Infinite loop using Worksheet_Calculate Parker Excel Discussion (Misc queries) 2 November 3rd 06 12:25 PM
For Next Infinite Loop Naji Excel Discussion (Misc queries) 5 January 13th 06 06:56 PM
Error Handler Not Working Bill Excel Discussion (Misc queries) 0 August 25th 05 07:13 PM


All times are GMT +1. The time now is 12:10 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"