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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,628
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
L

Legacy 456155

Guest
For what you are doing, this would probably be sufficient.
VBA Code:
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

Well-known Member
Joined
Feb 24, 2014
Messages
2,628
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Thank you data lover, this is exactly it :)
Tony
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,007
Different approach:
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,256
Messages
5,577,006
Members
412,760
Latest member
PetterL
Top