Friday, December 27, 2013

VBA - Removing Illegal characters for a sheet name

I'll often want a MACRO to name a worksheet based on user input. Users often enter characters that aren't allowed to be contained within a worksheet name though. The following lines strip all illegal characters out of a variable so it can be used as a worksheet name:


MyArray = Array("<", ">", "|", "/", "*", "\", "?", "[", "]", """")
    For X = LBound(MyArray) To UBound(MyArray)
        SheetName = Replace(SheetName, MyArray(X), "_", 1)
    Next X

No comments:

Post a Comment