preventing input of illegal characters in an inputbox

[drofnats]

Still a little new at vba. Can someone tell me how to
prevent someone from entering in spaces or any illegal
characters (like & % @ > etc) into an InputBox? At least
without writing a whole lot of "ElseIf" statements?

I have my script so it so names the range as whatever they
type. That's why I need to restrict entries. Thanks.

 

[Chip Pearson]

You can't prevent illegal characters in the input box. You'll
have to loop through the result of the InputBox testing each
character against a list of illegal characters.

 

[Chip Pearson]

You can't prevent illegal characters in the input box. You'll
have to loop through the result of the InputBox testing each
character against a list of illegal characters.

For example,


Const ILLEGAL_CHARS = "!@#$%^&*()"
Dim Ndx As Long
Dim Ndx2 As Long
Dim S As String
S = InputBox("Enter something")
For Ndx = 1 To Len(S)
For Ndx2 = 1 To Len(ILLEGAL_CHARS)
If Mid(S, Ndx, 1) = Mid(ILLEGAL_CHARS, Ndx2, 1) Then
MsgBox "Illegal characters"
End If
Next Ndx2
Next Ndx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Owen]

'THIS ERROR TRAPPING ROUTINE SHOULD WORK TOO


Sub AllowableNames()

Dim bErr As Boolean
Dim strInput As String
Do
On Error GoTo OopsErr
bErr = False
strInput = InputBox("Enter Range Name")

'***YOUR CODE HERE***
Range("F6:H13").Select
ActiveWorkbook.Names.Add Name:=strInput,
RefersToR1C1:="=Sheet1!R6C6:R13C8"

GoTo OK

OopsErr:
bErr = True
Resume Next

OK:

Loop Until bErr = False
End Sub

 

[Dave Peterson]

Another option is to just try to use the name and look for an error:

Option Explicit
Sub testme01()

Dim myStr As String
Dim myErr As Long

Do
myStr = InputBox("enter a string")
If Trim(myStr) = "" Then
'user cancelled
Exit Sub
Else
On Error Resume Next
ActiveSheet.Range("a1").Name = myStr
myErr = Err.Number
Err.Clear
On Error GoTo 0

If myErr <> 0 Then
'an error occurred
MsgBox "try again"
Else
Exit Do
End If
End If
Loop

End Sub

 

[DennisE]

Chip,

There's a much more efficient method
to test for illegal characters in an
input box than looping through the
characters as you suggested.
Suppose a given inputbox could contain
the ten digits, dollar sign, percent symbol, decimal point, commas, and plus or
minus sign, but nothing else. In that case you write:

If InputString Like "*[!0-9$%.,+-]*" Then
MsgBox "Sorry, the input field _ contains one or more illegal _
characters."
End If

If another inputbox was restricted to
digits and alphabetic characters alone,
you would replace the Like command above with Like "*[!0-9a-zA-Z]*"

Note that in either case the ! character immediately to the right of the left
bracket in the Like command acts like a negation operator.

-- Dennis Eisen

 

[David McRitchie]

Hi Dennis,

Nice, but actually you will have to put those characters in collating sequence
within the square brackets.

"*[!$%+,-.0-9]*" instead of "*[!0-9$%.,+-]*"
and
"*[!0-9A-Za-z]*" instead of "*[!0-9a-zA-Z]*"
same as immediately above but allow hyphen (minus) anywhere
"*[!-0-9A-Za-z]*"

Some information on Regular Expressions, I've put together
starting from a posting and information from Harlan Grove

Extraction of a Group of Digits and Dashes, from postings by Harlan Grove
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm

and of course you have to put the symbols in collating sequence

Excel characters as seen in the US (windows-1252)
: http://www.mvps.org/dmcritchie/rexx/htm/symbols_excel.htm

 

[David McRitchie]

Make that must be in "ASCII order" rather than "collating sequence"
because the order for sorting in Excel is not the "ASCII order".