asked on

InputBox, restricting special chars?

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

Gustav Brock

I can't replicate this.
There is nothing "special" with a string having a forward slash.

David Johnson, CD

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

RWayneH

ASKER

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

RWayneH

ASKER

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

RWayneH

ASKER

Except, now "TestAcc1" does not work. rats... and it has a number in it

David Johnson, CD

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

RWayneH

ASKER

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.

RWayneH

ASKER

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

RWayneH

ASKER

Any ideas?

ASKER CERTIFIED SOLUTION

RWayneH

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RWayneH

ASKER

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.