Check for special characters in vba for excel

Posted on 2009-04-23
Medium Priority
Last Modified: 2012-05-06
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.
Question by:tdiscover
  • 3
LVL 13

Accepted Solution

game-master earned 2000 total points
ID: 24221574

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

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

LVL 13

Expert Comment

ID: 24221584

just replace the "Folder" text to "Sheet"

i hope i could give u some idea..

Author Closing Comment

ID: 31574071
Sweet! Thanks champion!
LVL 13

Expert Comment

ID: 24221941

im glad i could help... :-)


Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question