Preventing invalid worksheet names

[Guest]

I have a spreadsheet where I name the worksheet based on a customer name in the worksheet. However, I need a formula or process to ensure that the customer name doesn't include an invalid character of : / \ ? * [ ] etc and to ensure it isn't longer than 31 characters.

Can anyone assist?

Thanks

 

[Peo Sjoblom]

If the names are typed in you can use data>validation> allow>custom

=AND(LEN(A1)<=31,A1<>"\",A1<>"/",A1<>"?",A1<>"*",A1<>"[",A1<>"]")

there are more characters that are not allowed in a file name but you can
add them as well

--

Regards,

Peo Sjoblom

I have a spreadsheet where I name the worksheet based on a customer name

in the worksheet. However, I need a formula or process to ensure that the
customer name doesn't include an invalid character of : / \ ? * [ ] etc and
to ensure it isn't longer than 31 characters.

 

[Dave Peterson]

Another option is to accept everything and just try to rename it--and catch any
errors:

with activesheet
on error resume next
.name = .range("f3").value
if err.number <> 0 then
msgbox "Invalid name in F3--please try again"
err.clear
end if
on error goto 0
end with

This'll catch duplicates and other problems (reserved names), too.

I have a spreadsheet where I name the worksheet based on a customer name in the worksheet. However, I need a formula or process to ensure that the customer name doesn't include an invalid character of : / \ ? * [ ] etc and to ensure it isn't longer than 31 characters.

Can anyone assist?

Thanks