• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 807
  • Last Modified:

Check for special characters in vba for excel

I have a function that adds a new spreadsheet to the workbook and names it. I get the name from a textbox but I want to check for special characters that aren't allowed when naming a spreadsheet (e.g apostrophes). I don't know exactly what characters aren't allowed (I've only experimented with apostrophes so far). A good solution will help me check for all invalid characters from the textbox.
0
tdiscover
Asked:
tdiscover
  • 3
1 Solution
 
game-masterCommented:

good morning
try sumthing like this...


Dim b As Byte, sMessage As String

For b = 1 To Len(vData)
    Select Case Asc(Mid(vData, b, 1))
        Case 42     'Star *
            sMessage = "A folder name cannot contain an asterisk ( * )"
        Case 47     'Slash /
            sMessage = "A folder name cannot contain a slash ( / )"
        Case 58     'Colon :
            sMessage = "A folder name cannot contain a colon ( : )"
        Case 60     'Less than <
            sMessage = "A folder name cannot contain a less than symbol ( < )"
        Case 62     'Greater than >
            sMessage = "A folder name cannot contain a greater than symbol ( > )"
        Case 63     'Question mark ?
            sMessage = "A folder name cannot contain a question mark ( ? )"
        Case 92     'Backslash \
            sMessage = "A folder name cannot contain a backslash ( \ )"
        Case 34     'Double quote "
            sMessage = "A folder name cannot contain a double-quote ( " & Chr(34) & " )"
        Case 124    'Pipe |
            sMessage = "A folder name cannot contain a pipe symbol ( | )"
    End Select
Next

If Len(sMessage) > 0 Then
    MsgBox sMessage, vbOKOnly, "Folder path validation"
    mtxt_folder_name = Empty
Else
    mtxt_folder_name = vData
End If

game-master
0
 
game-masterCommented:

just replace the "Folder" text to "Sheet"


i hope i could give u some idea..
0
 
tdiscoverAuthor Commented:
Sweet! Thanks champion!
0
 
game-masterCommented:

im glad i could help... :-)


game-master
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now