I'm almost done writing a little "LegalWorkSheet()" function that accepts the
proposed worksheet name and returns a version of it that's guaranteed not to
trap out when assigned to a worksheet.

One thing I haven't done yet is to iterate through all existing sheets to see if
any are the same as the one I just concocted and modify the new one if
needed...and then check again.

Can anybody point me to some other code that does this - perhaps in a more
elegant and correct way?

Here's what I have so far:
---------------------------------------

Const mExcelWorkSheetNameLen_Lim As Long = 29 'Error 1004 says 31, but
experimentation suggests 29 is the actual limit

Public Function LegalWorksheetName(ByVal theWorkSheetName As String) As String
debugStackPush mModuleName & ": LegalWorksheetName"
On Error GoTo LegalWorksheetName_Err

' PURPOSE: To make sure that a proposed worksheet name is legal per Excel's
standards
' ACCEPTS: The string to be converted
' RETURNS: A string with any illegal characters replaced with underscores
'
' NOTES: 1) We originally chose which chars to replace from the following
' error thrown by Excel when we tried to feed it
' an unacceptable name and then added "^" and dropped the length
limit
' from experience...
' -----------------------------------------------------------------
' Error 1004: While renaming a sheet or chart, you entered an
invalid name.
' Try one of the following:
' ? Make sure the name you entered does not exceed 31
characters.
' ? Make sure the name does not contain any of the
following characters: : \ / ? * [ or ]
' ? Make sure you did not leave the name blank.

Dim myBadBoyz() As Variant

Dim i As Integer
Dim myWorkSheetName As String
Dim myArraySize As Long
Dim myRemainder As Long

Const myBenignChar As String = "x"
Const myTrimLen_Left As Long = 15
Const myTrimLen_Rite As Long = mExcelWorkSheetNameLen_Lim - myTrimLen_Left

myBadBoyz = Array(":", "\", "/", "?", "*", "[", "]", "^")
myArraySize = UBound(myBadBoyz)

myWorkSheetName = theWorkSheetName
For i = 0 To myArraySize
myWorkSheetName = Replace(myWorkSheetName, myBadBoyz(i), myBenignChar)
Next i

If Len(myWorkSheetName) <= mExcelWorkSheetNameLen_Lim Then
LegalWorksheetName = myWorkSheetName
Else
LegalWorksheetName = Left$(myWorkSheetName, myTrimLen_Left) &
Right$(myWorkSheetName, myTrimLen_Rite)
End If

LegalWorksheetName_xit:
DebugStackPop
On Error Resume Next
Exit Function

LegalWorksheetName_Err:
BugAlert True, ""
Resume LegalWorksheetName_xit
End Function
---------------------------------------
--
PeteCresswell