Reject Special Characters in Input Box string VBA

[Shiremaid]

I have a spreadsheet with a macro to add new countries. The macro adds a new sheet for each country and users enter the name into an Input Box.
I have the code to reject country names with spaces (because it messes up later steps) but special characters other than underscore also mess it up.
I added code for the two special characters someone tried to use, but is there a simple way to check the string they enter for multiple disallowed characters instead of having an ElseIf for each special character I want to disallow?
I have searched many many times for a solution on google, but all the answers I find are either really old and no longer work or far beyond my google-learned VBA skills to adapt to my workbook.

The code I have is

    Dim response As String
    response = InputBox("Country Name (no spaces)")
    Dim errormess As String
    errormess = "Do not use spaces or special characters (except underscore) in country names."



    If InStr(1, response, " ") > 0 Then
        MsgBox (errormess)
        Exit Sub
     ElseIf InStr(1, response, "-") > 0 Then
        MsgBox (errormess)
        Exit Sub
    ElseIf InStr(1, response, "&") > 0 Then
        MsgBox (errormess)
        Exit Sub


    Else
        Sheets("aaSiteTemplate").Copy Before:=Sheets("aaSiteTemplate")
        ActiveSheet.Range("A1") = response
    End If
    ActiveSheet.Name = response

 

[Joe4]

I think I would go the other way, and tell it what entries are valid (upper case letters, lower case letters, numbers, underscore).
This question has come up quite a few times over the years on this forum.
Here is one thread that has both a VBA and non-VBA solution: https://www.mrexcel.com/forum/excel...n-alphanumeric-other-specific-characters.html
If it were me, I might be tempted to create my own function in VBA used to validate any value you pass it.

 

[Shiremaid]

I'm sorry, as I said, I learned VBA by googling things. I don't understand what the code you linked to is doing or how I could add any of that to my existing code.
I understand what the code I have is doing and prefer to stick with code I do understand since I'm the one who needs to fix it.
If there isn't a simple/basic way to do this, I would rather stick to what I have as there are only so many special characters they're likely to use.

 

[Joe4]

there are only so many special characters they're likely to use.

"Likely" is a tricky word, when it comes to users. Any time you think you know what they are going to do, they often prove you wrong (learned that over many years of experience).
To me, it is much safer to check for what is allowed, rather than what is not allowed, as what is allowed is much more defined/concrete. On top of the "normal" special characters, users could conceivably add other special characters, so there is no guarantee that the method of checking for certain non-allowed characters is going to work 100% of the time.

That being said, if you really want to follow with the logic you are using, you can store all the characters to check for in an array, and then loop through that array. That code would look something like this:

Sub InsertCountry()

    Dim CheckChars As Variant
    Dim response As String
    Dim i As Long
    
    response = InputBox("Country Name (no spaces)")
    Dim errormess As String
    errormess = "Do not use spaces or special characters (except underscore) in country names."

'   Set unallowed characters
    CheckChars = Array(" ", "-", "&")
    
'   Loop through each value to check for
    For i = LBound(CheckChars) To UBound(CheckChars)
        If InStr(1, response, CheckChars(i)) > 0 Then
            MsgBox (errormess)
            Exit Sub
        End If
    Next i

'   Insert new sheet
    Sheets("aaSiteTemplate").Copy Before:=Sheets("aaSiteTemplate")
    ActiveSheet.Range("A1") = response
    ActiveSheet.Name = response

End Sub

So, you would just add whatever characters you would like to check for in your Array.

 

[Shiremaid]

Thank you.
I understand mostly what this is doing, but what are LBound and UBound?

 

[Joe4]

When you are working with Arrays, you don't necessarily know how many items are in your array.
LBound (lower bound) is a dynamic property that selects the first item in your array, while UBound (upper bound) is the last item in your array.
So, you are just dynamically looping from the first item to the last in the array.

 

[Shiremaid]

Thank you very much for you help and explanation. Arrays and loops are something I always have trouble with, but it's starting to make sense.

 

[Rick Rothstein]

The code I have is

    Dim response As String
    response = InputBox("Country Name (no spaces)")
    Dim errormess As String
    errormess = "Do not use spaces or special characters (except underscore) in country names."

    If InStr(1, response, " ") > 0 Then
        MsgBox (errormess)
        Exit Sub
     ElseIf InStr(1, response, "-") > 0 Then
        MsgBox (errormess)
        Exit Sub
    ElseIf InStr(1, response, "&") > 0 Then
        MsgBox (errormess)
        Exit Sub

    Else
        Sheets("aaSiteTemplate").Copy Before:=Sheets("aaSiteTemplate")
        ActiveSheet.Range("A1") = response
    End If
    ActiveSheet.Name = response

Replace what I highlighted in red with this...

    If response Like "*[!A-Za-z_]*" Then
        MsgBox (errormess)
        Exit Sub

 

[Shiremaid]

Thank you Rick! I saw something like that somewhere but it was buried in a long complicated bit of code. This works perfectly

 

[Joe4]

Thank you Rick! I saw something like that somewhere but it was buried in a long complicated bit of code. This works perfectly

Yep, that is a nice solution that limits you to what to accept, instead of what not to accept.