Macro to check words in cell dont use things like "/" in the file name??

[tonywatsonhelp]

Hi Everyone,

please excuse my ignorance but i don't know what the official name for these things would be, but if you use excel a lot I'm sure you can understand what I'm trying to do

I use cell D11 as the cell that holds the document name

I have a macro that works great, that takes this name and saves the document in it current location.
this is all fine,
the problem is sometimes someone will try save the document using a character excel cant include in the file name easily and we get an error
for example , it might be named "21/23 the street"

so what I'd like is a way to check if the cell contains any problem letters ?
if it does, the ideal would be to tell the person what is being used that cant be used in a message box?? eg "You can not use / in a document name, please change this!"

anyone able to help me? I don't know which chartreus are a problem but even if you could just do it to check the propular ones like "/" then this would be a huge help

thanks

Tony

 

[Legacy 456155]

For what you are doing, this would probably be sufficient.

Public Function IsValidFileName(FileName As String) As Boolean
    IsValidFileName = Not FileName Like "*[<>:/\|?*]*"
End Function

Sub YourCurrentSub()
    If Not IsValidFileName Then
        MsgBox "Message to user"
        Exit Sub
    End If
End Sub

 

[tonywatsonhelp]

Thank you data lover, this is exactly it
Tony

 

[mumps]

Different approach:

Sub IterateCharacters_String()
    Dim sStr As String, Chars As Variant, i As Long
    Chars = Array("\", "/", ":", "*", "?", "<", ">", "|", "[", "]", """")
    With Sheets("Sheet1").Range("D11")
        For i = LBound(Chars) To UBound(Chars)
            If InStr(.Value, Chars(i)) > 0 Then
                If sStr = "" Then sStr = Chars(i) Else sStr = sStr & " " & Chars(i)
            End If
        Next i
    End With
    MsgBox ("You can not use the following characters in a document name." & Chr(10) & sStr & Chr(10) & "Please change them!")
End Sub

You can add characters to the array if necessary.