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 = ""
VBAMicrosoft ExcelMicrosoft Office
Last Comment
RWayneH
always check user input
This code will reject anything except alphabetic a-z/A-Z and numbers
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
ASKER
I can deal with placing the input value somewhere. How would I write "lastrow" out of suggested solution?
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 = ""
ASKER
Except, now "TestAcc1" does not work. rats... and it has a number in it
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
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
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.
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
ASKER
Any ideas?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
There is nothing "special" with a string having a forward slash.