InputBox, restricting special chars?

RWayneH
RWayneH used Ask the Experts™
on
Is there a way to restrict special characters, on an inputbox?  I was testing this and my mouse fell on the keyboard, adding a / character, so I went with it and oddly it made the procedure fail.  Either I need to Dim strNewListItemName as something different or add another ElseIf.  Please advise and thanks.  ( way odd to find this failure that way.  lol)

Do
        strNewListItemName = InputBox("Please enter an account name.", "Enter new account name", SampleName)
        If strNewListItemName = "New Account name." Then
            MsgBox ("You need to change the default account input")
        ElseIf StrPtr(strNewListItemName) = 0 Then
            Exit Sub
        ElseIf strNewListItemName = "" Then
            MsgBox ("Can not be blank or use the default input text")
        ElseIf Len(strNewListItemName) > 30 Then
            MSG1 = MsgBox("Your account name exceeded 30 characters, try shortening the account name.", vbOKOnly + 48, "Warning, Error")
        End If
    Loop Until Len(strNewListItemName) < 31 And Not strNewListItemName = "New Account name." And Not strNewListItemName = ""

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I can't replicate this.
There is nothing "special" with a string having a forward slash.
Top Expert 2016

Commented:
always check user input
This code will reject anything except alphabetic a-z/A-Z and numbers
Sub test()
Dim name As String
lastrow = Sheet1.Range("A65536").End(xlUp).Row + 1
name = InputBox("Enter your name")
For i = 1 To Len(name)
     If VBA.Asc(VBA.Mid(name, i, 1)) >= 65 And VBA.Asc(VBA.Mid(name, i, 1)) <= 90 Or _
     VBA.Asc(VBA.Mid(name, i, 1)) >= 97 And VBA.Asc(VBA.Mid(name, i, 1)) <= 122 Or _
     IsNumeric(name) Then Sheet1.Cells(lastrow, 1).Value = name
    Else
          MsgBox "Only text or only numbers can be entered", vbCritical
     Exit For
     End If
Next
End Sub

Open in new window

Author

Commented:
I can deal with placing the input value somewhere.  How would I write "lastrow" out of suggested solution?
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Author

Commented:
This could probably be cleaned up a bit, but I got this to work.  I hate having to use Goto ...

 Do
300
        strNewListItemName = InputBox("Please enter an account name.", "Enter new account name", SampleName)
        For i = 1 To Len(strNewListItemName)
            If VBA.Asc(VBA.Mid(strNewListItemName, i, 1)) >= 65 And VBA.Asc(VBA.Mid(strNewListItemName, i, 1)) <= 90 Or VBA.Asc(VBA.Mid(strNewListItemName, i, 1)) >= 97 And VBA.Asc(VBA.Mid(strNewListItemName, i, 1)) <= 122 Then
            Else
                 MsgBox "Input can only be text or numbers, try again.", vbCritical
            GoTo 300
            End If
        Next
        
        If strNewListItemName = "New Account name." Then
            MsgBox ("You need to change the default account input")
        ElseIf StrPtr(strNewListItemName) = 0 Then 
            Exit Sub
        ElseIf strNewListItemName = "" Then
            MsgBox ("Input can not be blank or it cannot use the default input text")
        ElseIf Len(strNewListItemName) > 30 Then
            MSG1 = MsgBox("Your account name exceeded 30 characters, try shortening the account name.", vbOKOnly + 48, "Warning, Error")
        End If
    Loop Until Len(strNewListItemName) < 31 And Not strNewListItemName = "New Account name." And Not strNewListItemName = ""

Open in new window

Author

Commented:
Except, now "TestAcc1" does not work.  rats... and it has a number in it
Top Expert 2016

Commented:
you're missing parts of your code
Do without a while or until
Your first test has an if then an empty then followed by an else
valid = false 
Do While valid = false
        strNewListItemName = InputBox("Please enter an account name.", "Enter new account name", SampleName)
        For i = 1 To Len(strNewListItemName)
            If VBA.Asc(VBA.Mid(strNewListItemName, i, 1)) >= 65 And _
		VBA.Asc(VBA.Mid(strNewListItemName, i, 1)) <= 90 Or _
		VBA.Asc(VBA.Mid(strNewListItemName, i, 1)) >= 97 And _
		 VBA.Asc(VBA.Mid(strNewListItemName, i, 1)) <= 122 Then valid = true
            Else
                 MsgBox "Input can only be text or numbers, try again.", vbCritical
            End If
Loop    

Open in new window

Author

Commented:
Please review the code I posted that works, except from with numbers.  The Loop Until for the Do is on Ln21.  Your new suggested code is not working, does not like Ln9 (Else with no If ) and no Next for the For loop.  I tried folding in If/ElseIf statements after your For Next, but having issues.  Still working with your code to get that to work.

Author

Commented:
Ok, my testing has narrowed it down to the following: (when a special char it finds it, but thinks a number is a special char)

If VBA.Asc(VBA.Mid(strNewListItemName, i, 1)) >= 65 And _
                VBA.Asc(VBA.Mid(strNewListItemName, i, 1)) <= 90 Or _
                VBA.Asc(VBA.Mid(strNewListItemName, i, 1)) >= 97 And _
                 VBA.Asc(VBA.Mid(strNewListItemName, i, 1)) <= 122 Then
         
            Else
               MsgBox "Input can only be text or numbers, try again.", vbCritical
               GoTo 300
            End If

Open in new window

Author

Commented:
Any ideas?
Commented:
After reviewing an VBA.Asc char matrix chart, I landed on this and it seems to be working pretty well.
InPutBox that only allows for text, numbers and hyphen.  Also does not allow to keep default text, input cannot be blank, can only be 30 chars long and exits if the Cancel or upper right hand corner X is pressed.  Pretty nice.

Sub AddNewAccountAndReSortIt()
Dim strNewListItemName As String
Dim i As Integer
Dim Valid As Boolean
    Application.ScreenUpdating = False
    SampleName = "New Account name."
    ActiveWorkbook.Unprotect
    ActiveSheet.Unprotect
    Do
300
        strNewListItemName = InputBox("Please enter an account name.", "Enter new account name", SampleName)
        For i = 1 To Len(strNewListItemName)
            If VBA.Asc(VBA.Mid(strNewListItemName, i, 1)) >= 48 And _
                VBA.Asc(VBA.Mid(strNewListItemName, i, 1)) <= 57 Or _
                VBA.Asc(VBA.Mid(strNewListItemName, i, 1)) >= 65 And _
                 VBA.Asc(VBA.Mid(strNewListItemName, i, 1)) <= 90 Or _
                 VBA.Asc(VBA.Mid(strNewListItemName, i, 1)) >= 97 And _
                 VBA.Asc(VBA.Mid(strNewListItemName, i, 1)) <= 122 Or _
                 VBA.Asc(VBA.Mid(strNewListItemName, i, 1)) = 45 Then
            Else
               MsgBox "Input can only be text or numbers, try again.", vbCritical
               GoTo 300
            End If
        Next
        If strNewListItemName = "New Account name." Then
            MsgBox ("You need to change the default account input")
        ElseIf StrPtr(strNewListItemName) = 0 Then  'important one, for the Cancel click or the X in upper right corner, NOTE: StrPtr
            Exit Sub
        ElseIf strNewListItemName = "" Then
            MsgBox ("Input can not be blank or it cannot use the default input text")
        ElseIf Len(strNewListItemName) > 30 Then
            MSG1 = MsgBox("Your account name exceeded 30 characters, try shortening the account name.", vbOKOnly + 48, "Warning, Error")
        End If
    Loop Until Len(strNewListItemName) < 31 And Not strNewListItemName = "New Account name." And Not strNewListItemName = ""
'Done with InputBox stuff

' do something with the input or other excel stuff.

End Sub

Open in new window

Author

Commented:
Thanks for your help with this.  You led me to how to deal with this, and got me started.  After messing and digging a little deeper I was able to figure it out, and I now have an awesome InputBox that has a bunch of rules/validations on it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial