|
Posted to microsoft.public.excel.programming
|
|
|
Rename sheet by removing any invalid character
On Jan 13, 9:30 pm, "Rick Rothstein"
wrote:
Here is a function you can use to "fix" the proposed name by purging it of
the bad characters and truncating the proposed name to no more than 31
characters..
Function FixedName(ProposedName As String) As String
Dim V As Variant
FixedName = ProposedName
For Each V In Array("\", "/", "?", "*", "[", "]")
FixedName = Replace(FixedName, V, "")
Next
FixedName = Left(FixedName, 31)
End Function
So, after installing this function (probably best to put it into a Module...
click Insert/Module from the VB menu bar), just do this in your code...
ActiveSheet.Name = FixedName(ActiveCell.Value)
--
Rick (MVP - Excel)
"al" wrote in message
...
I'm currently using macro below to remove / as invalid character and
keep the character limit to 31. However i would also like to add the
other invalid characters ? \ [ ] * so that i can provide for more
invalid characters - Pls help thxs
Sub SheetNameActivecell()
Application.ActiveSheet.Name = Left(Application.Substitute
(ActiveCell.Value, "/", ""), 31)
End Sub
thxs
|