Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
preventing input of illegal characters in an inputbox
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
preventing input of illegal characters in an inputbox
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
preventing input of illegal characters in an inputbox
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
preventing input of illegal characters in an inputbox
'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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
preventing input of illegal characters in an inputbox
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
preventing input of illegal characters in an inputbox
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
preventing input of illegal characters in an inputbox
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. . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
preventing input of illegal characters in an inputbox
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
preventing input of illegal characters in an inputbox
Make that must be in "ASCII order" rather than "collating sequence"
because the order for sorting in Excel is not the "ASCII order". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dependent Data Validation with Illegal Characters | Excel Discussion (Misc queries) | |||
Preventing data input until a particular cell is used | Excel Worksheet Functions | |||
Preventing Excel Product Piracy or Illegal Giveaways | Excel Discussion (Misc queries) | |||
Check on the input in an inputbox | Excel Programming | |||
String length of Inputbox user input. | Excel Programming |