Solved: Text Compare - pattern match

[Glaswegian]

Hi - back again...

On a userform, I'm trying to check the input of two particular fields. Both are textboxes. One box requires only numerals and my code for testing that works fine.

Else 'check that only numerals have been input...
If Not txbAssCost.Text Like String(Len(txbAssCost.Text), "#") Then
MsgBox "Only input the amount in figures.", vbInformation, "Invalid Input"
txbAssCost.Value = ""
Cancel = True
txbAssCost.SetFocus 'reset the focus back to the empty field
Else 'if the input is OK, update some sheet values...

However, my problem is the trying to do the same in the other textbox, except this time the field requires only letters - no numerals are permitted. I therefore used the above working code to adapt the LIKE operator and match against the whole alphabet, but I can't get it to work - i get a "type mismatch" message.

Else 'if the input is OK, update some sheet values...
If Not txbPartName.Text Like String(Len(txbPartName.Text), [a-z]) Then
MsgBox "Do not input numerals in this field.", vbInformation, "Invalid Input"
txbPartName.Value = ""
Cancel = True
txbPartName.SetFocus 'reset the focus back to the empty field
Else
I guess this is something to do with the way I've set up [charlist] but I can't seem to work out the correct syntax.

Any help greatly appreciated before advanced senility sets in...

[Charlize]

'option compare text else only A, B, C ... Z
Option Explicit
Option Compare Text
Private Sub CommandButton1_Click()
Dim answer As Boolean
answer = TextBox1.Value Like "[A-Z]"
If answer = False Then
MsgBox "Do not input numerals in this field.", vbInformation, "Invalid Input"
TextBox1.Value = ""
TextBox1.SetFocus 'reset the focus back to the empty field
Else
MsgBox "okidoki"
Unload Me
End If
End Sub
Charlize

[RichardSchollar]

I don't think you can use String like that (it only wants a single character to repeat). You could use a function which utilises Regular Expressions - I know there's some KB entries dealing with this. One possible function would be:

Function CompareString(s As String, Optional iType As Long = 0) As Boolean
Dim strNum As String, strAlpha As String
strNum = "^\d+$"
strAlpha = "^[A-Za-z]+$"
With CreateObject("VbScript.Regexp")
If iType=0 Then
.Pattern = strNum
Else
.Pattern = strAlpha
End If
.Global = True
CompareString = .Test(s)
End With
End Function


Then you can use it in place of the String(Len)) construct - the optional parameter specifies that if 0 (or omitted) then the function tests for the string containing only digits, if 1 (or anything else) then tests for the string containing only alpha characters:

...code snippet... 'test for digits
If Not CompareString(txbAssCost.Text) Then
MsgBox "Only input the amount in figures.", vbInformation, "Invalid Input"
...code snippet...

and

...code snippet...
If Not CompareString(txbPartName.Text,1) Then 'check for only alphas
MsgBox "Do not input numerals in this field.", vbInformation, "Invalid Input"
...code snippet...


Hope this helps!

Richard

[Glaswegian]

Hi guys

Thanks to both - however the code still goes to the message box in both cases, with alpha or alphanumeric. I've seen RegExp used before but I only have a very rough idea of what it's about. Trouble is I'm so rusty as this nowadays...

Richard, I'm guessing that there's something in the variable settings that's perhaps causing the problem? Wouldn't be the "^" would it? Nice UDF though - I like it.

[Charlize]

Attached a little workbook to show what I meant. Try inputting :
- a
- 123
- a123
- 12a
or any other combination from a,A to z,Z

Charlize

ps.: have forgotten to check on empty value.

[RichardSchollar]

Shouldn't be - the ^ marks the start of the string and the $ indicates the end. i used these to ensure that everything between the start and end is of the type (numeric digit or alpha character) indicated.

Possibly, there is an additional character (eg a line terminator) coming across (I know this happens in some function calls in C - but I'll be the first to admit I have no idea whether this occurs in VBA). Possibly give it a try with the following modification:

If Not CompareString(Application.WorksheetFunction.Clean(txbPartName.Text),1) Then 'check for only alphas
MsgBox "Do not input numerals in this field.", vbInformation, "Invalid Input"
...etc etc...

and see if that improves matters.

Richard

[Charlize]

Seems that there is only a check on the first character.

Charlize

[johnske]

Hi - back again...

On a userform, I'm trying to check the input of two particular fields. Both are textboxes. One box requires only numerals and my code for testing that works fine.

Else 'check that only numerals have been input...
If Not txbAssCost.Text Like String(Len(txbAssCost.Text), "#") Then
MsgBox "Only input the amount in figures.", vbInformation, "Invalid Input"
txbAssCost.Value = ""
Cancel = True
txbAssCost.SetFocus 'reset the focus back to the empty field
Else 'if the input is OK, update some sheet values...

However, my problem is the trying to do the same in the other textbox, except this time the field requires only letters - no numerals are permitted. I therefore used the above working code to adapt the LIKE operator and match against the whole alphabet, but I can't get it to work - i get a "type mismatch" message.

Else 'if the input is OK, update some sheet values...
If Not txbPartName.Text Like String(Len(txbPartName.Text), [a-z]) Then
MsgBox "Do not input numerals in this field.", vbInformation, "Invalid Input"
txbPartName.Value = ""
Cancel = True
txbPartName.SetFocus 'reset the focus back to the empty field
Else
I guess this is something to do with the way I've set up [charlist] but I can't seem to work out the correct syntax.

Any help greatly appreciated before advanced senility sets in...After the "Else"...

Dim N As Long
With txbPartName
For N = 1 To Len(.Text)
If IsNumeric(.Characters(N, 1).Text) Then
MsgBox "Do not input numerals in this field.", vbInformation, "Invalid Input"
.Value = ""
Cancel = True
.SetFocus 'reset the focus back to the empty field
Exit For
End If
Next
End With

[Charlize]

In a moment of enlightment I came up with this. Check the inputfield when you are inputting with the change event.
Option Compare Text
Private Sub TextBox1_Change()
If Len(TextBox1.Value) > 0 Then
If Not Mid(TextBox1.Value, Len(TextBox1.Value), 1) Like "[A-Z]" Then
MsgBox "Do not input numerals in this field.", vbInformation, "Invalid Input"
End If
End If
End Sub

Charlize

[Glaswegian]

Apologies for the late reply - work intervened.

Thanks to all for your help. For the moment I've gone with Charlize's solution, but I've put the others away so that I can work on them in the future.

Cheers!

[johnske]

The way I read it was that you wanted to exclude ALL numbers from the string, not just exlude the last entry in the string from being a number :dunno

[Charlize]

The way I read it was that you wanted to exclude ALL numbers from the string, not just exlude the last entry in the string from being a number :dunno
If there is a clever guy/girl who doesn't remove the last typed character from the textbox then there will be, of course, unwanted characters in the field (but they get a messagebox to warn them). Probably built something in to return the string -1 to the textbox.

Try this code :
Private Sub TextBox1_Change()
If Len(TextBox1.Value) > 0 Then
If Not Mid(TextBox1.Value, Len(TextBox1.Value), 1) Like "[A-Z]" Then
MsgBox "Do not input numerals in this field.", vbInformation, "Invalid Input"
If Len(TextBox1.Value) > 1 Then
TextBox1.Value = Mid(TextBox1.Value, 1, Len(TextBox1.Value) - 1)
Else
TextBox1.Value = vbNullString
End If
End If
End If
End Sub

Charlize

ps.: Perhaps include ME keyword before textbox1.value.

[johnske]

That's not what I meant, as an example aaaaaa2 gave the message but 222222a did not

[Charlize]

That's not what I meant, as an example aaaaaa2 gave the message but 222222a did not
You've used my last version of the textbox change event ?

Charlize

[Glaswegian]

John

Your code gives me a "Method or data member not found" error on ".Characters"?

BTW - you are correct - my intention is to exclude ANY number.

[johnske]

It's basically the same as the other - Mid(TextBox1.Value, Len(TextBox1.Value), 1) only returns the last character in the string :)

[Charlize]

A example of textbox where no numbers, spaces, +/-* ... are allowed.

Charlize

[johnske]

John

Your code gives me a "Method or data member not found" error on ".Characters"?

BTW - you are correct - my intention is to exclude ANY number.Try Instr then...

With TextBox1
For N = 0 To 9
If Not InStr(1, .Text, N) = 0 Then
MsgBox "Do not input numerals in this field.", vbInformation, "Invalid Input"
.Value = ""
Cancel = True
.SetFocus 'reset the focus back to the empty field
Exit For
End If
Next
End With

[Glaswegian]

Cheers John - works a treat now.

[johnske]

Had a rethink about this Iain - this'll be faster (avoids a loop)

With TextBox1
If .Text Like "*[0-9]*" Then
MsgBox "Do not input numerals in this field.", vbInformation, "Invalid Input"
.Value = ""
Cancel = True
.SetFocus 'reset the focus back to the empty field
End If
End With