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.

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.





"drofnats" wrote in message
...
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.

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




"drofnats" wrote in message
...
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,

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

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/...bols_excel.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"DennisE" wrote in message ...
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

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

'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



-----Original Message-----
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.


.

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



drofnats wrote:

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.

--

Dave Peterson

Thanks everyone for your suggestions. I got it working :)


-----Original Message-----
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.


.