Thank you again but as you note this brute-force method is slow, especially
since there could be multiple invalid characters and in my case the Excel
error comes back as an exception passed through COM interops. There's got to
be a better way. Surely at a minimum Microsoft must publish a list of
characters that are invalid in worksheet names.

- Drew

"Die_Another_Day" wrote:

Well, you can use the same approach to find the invalid character,
however inefficient it may be....
For cnt = 1 to Len(Name)
ws.name = Mid(Name,cnt,1)
if err.Number = 1004 then Exit For
Next
Name = Left(Name,cnt - 1) & "_" & Right(Name,Len(Name) - cnt)

Maybe one of the MVP's will have a better solution but that should get
you going.

Charles

Drew Lettington wrote:
Thanks for the reply but just knowing the proposed worksheet name is invalid
isn't helpful. The code needs to create a valid name. For example, the user
provides a name of abc/def and my code converts it to abc_def. My code needs
to know which characters are invalid so they can be replaced.

- Drew

"Die_Another_Day" wrote:

Try setting an object to the worksheet that you want to name, then use
error checking to test if the name was valid or not. Something like
this:
Sub TestName()
Dim Name As String
Dim ws As Object
Name = InputBox("Test Name")
On Error Resume Next
Set ws = Sheets(2)
ws.Name = Name
If Err.Number = 1004 Then '1004 is the error number returned for
invalid charactor
MsgBox "Invalid Name"
End If
On Error GoTo 0
End Sub

HTH

Charles
Drew Lettington wrote:
I have some code that creates a new worksheet from a name provided by a user.
The code checks the user input for invalid characters before creating the
name, eliminating : @ \ / ? * [ ]. The code worked fine running English
Excel but when I ran using Japanese Excel I had a problem.

In the Japanese environment the user provided a name that contained the yen
(￥) character. My code checked for \ which is the directory path separator
in Japanese and displays as the yen symbol. But since the user's name had
the actual yen character my code failed to create a valid worksheet name.

I could modify my code to explicitly check for the yen character and it
would fix the problem. However, yen character is valid in English Excel so I
don't want to replace it in that environment. I also can't just check thread
locale or regional settings or something similar as English Excel could be
running on a Japanese system.

My question then, is it possible to programatically determine at runtime
which characters are invalid in worksheet names? For example, is there an
Excel call that would return a list of invalid characters which I could then
removed from any proposed worksheet names?

- Drew