I need to test the string that will be entered into an InputBox to detect if there are any numbers in the string, eg. AB4C, 9hgTRe, CvFgr54Xt etc - these need to be caught. The string can be of any length.
I have a feeling that this may involve Regex but I hope not 🙂
Thanks!
You can simply use the Like operator:
If "aaz1" like "*#*" then 'it has digits
But best is to restrict what users are allowed to type.
Application.InputBox is different than just InputBox, see this article. The first calls the InputBox method, the second calls the InputBox function.
You can restrict values to text only, or to numbers, ranges, or combinations.
For complex restrictions, you can take a look at this article, with user forms, this allows you to force users to type values in a specific range.
Thanks mate, I didn't know there were two types of InputBox ... <sigh>
Anyway I tested it and it didn't work in the sense that it allowed me to enter a number when I suppose it shouldn't have.
Here is my code:
Dim TestString As Variant
TestString = Application.InputBox(prompt:="Please enter your text", Title:="Export File Data", Type:=2)
MsgBox "You have entered: " & TestString
This didn't object to me entering a number. What should have happened? What am I doing wrong?
The input box will return a text string, even if you type only numbers in InputBox, even if Type:=2.
you can test the type of the result with : MsgBox TypeName(TestString)
Type:=2 will return String, no matter what chars you type, if Type:=1 will return Double.
You wil have to check the result with: If "aaz1" like "*#*", or use the link I sent to use a userform instead to restrict the input to alpha only.
OK so that's a fail - I'm not sure about the purpose of Application.InputBox or why its different to the InputBox function after all. According to MS the Type parameter "Specifies the return data type. If this argument is omitted, the dialog box returns text.", but it seems it always returns text no matter what. I don't see how this can enable you to "... restrict values to text only, or to numbers, ranges, or combinations."
Anyway, I'll have to come up with something else ...
Hi Kevin,
I'm not sure what isn't working for you. Doing as Catalin suggests works fine for me e.g.
If TestString Like "*#*" Then Debug.Print "Numbers"
will detect if a number has been typed in. Once you detect this you then have to prompt for input again.
Regards
Phil
