Check for special characters in vba for excel

Posted on 2009-04-23
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
    LVL 13

    Accepted Solution


    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


    just replace the "Folder" text to "Sheet"

    i hope i could give u some idea..

    Author Closing Comment

    Sweet! Thanks champion!
    LVL 13

    Expert Comment


    im glad i could help... :-)


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Suggested Solutions

    Article by: Leon
    Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now