VBA to check if textbox only contains A-Z, a-z, hyphen and spaces

[alistercruickshanks]

Hi,

I am struggling trying to check if a textbox contains characters that are Not A-Z, or a-z or a Hyphen or a Space

Our access system exports names to an accounts system that breaks if a foreign character is in the name, like accents.

The solution would be to warn users with a msgbox popup..... that the name contains such characters

The textboxes are autofilled by extracting data, so I don't want to use a mask, but use code to check after we export.

So.... If I am looking for

If [textbox name] is Not Like..... then
msgbox
End if


I have tried a varierty of code, but I think most is for ASP..NET or SQL

If anyone has any soltion for VBA would be great.


So... Only allow letters in upper or lower case, or spaces, or hyphens (for double barrel names). Otherwise warn the user with a popup.

Thank you.

 

[Minty]

Have a look a using Regular Expressions:

VBA – Using Regular Expressions (RegEx) | DEVelopers HUT

Using Regular Expressions, common referred to as RegEx, is a great tool for very quickly parsing strings and typically will be faster than other VBA approaches.

www.devhut.net

 

[CJ_London]

If this is for people’s names, what about quotes? e.g. D’arville. And depending on how the name is sourced, might include the standard quote or the smart quote

 

[alistercruickshanks]

If this is for people’s names, what about quotes? e.g. D’arville. And depending on how the name is sourced, might include the standard quote or the smart quote

I may need to test those, a good point. I think from memory standard quotes are ok, so can add in, but smart quotes I may need to use replace for

 

[DickyP]

Definitely a task for Regular Expression.

Also what about foreign names with umlauts, hyphens, ^, ´, ` etc.,

 

[CJ_London]

if you also need addresses and titles you will also have the full stop to consider

St. James
Revd. Smith

 

[KitaYama]

Since I've never been able to remember all patterns in regex, I do my validations with VBA.
Not as powerful as regex, but if it works, it works. I'm too lazy to learn what each of those numerous characters in regex stand for.

The following, Returns true if the passed text contains only space and alphanumerical and Hyphon.
Otherwise, it returns false. (text contains / * + $ # or others)

Public Function txtIsValid(TextToValidate) As Boolean

    txtIsValid = Not TextToValidate Like "*[!0-9 a-zA-Z]*" Or InStr(TextToValidate, "-")

End Function

If necessary, you can add other characters to the pattern too.
For example : "*[!0-9 .'a-zA-Z]*" returns false if the passed text contains any character outside the range of the following list:
alphanumerical , hyphen, Space, dot (.) and single quote (')

 

[arnelgp]

you may also try this udf (provided by ChatGPT).

To check if a filename in Microsoft Access VBA contains valid characters (i.e., it doesn't include diacritical marks or non-printable characters), you can use a VBA function that performs the following tasks:

' chatgpt
Function IsValidFilename(filename As String) As Boolean
    Dim i As Integer
    Dim charCode As Integer
    Dim valid As Boolean
    valid = True
    
    ' Loop through each character in the filename
    For i = 1 To Len(filename)
        charCode = Asc(Mid(filename, i, 1))
        
        ' Check for non-printable characters (ASCII codes 0-31 and 127)
        If (charCode < 32 Or charCode = 127) Then
            valid = False
            Exit For
        End If
        
        ' Check for diacritical marks (Unicode range)
        ' Diacritical marks are in the Unicode range 0x0300 to 0x036F
        ' Check if the character has a diacritical mark
        If (charCode >= 768 And charCode <= 879) Then
            valid = False
            Exit For
        End If
    Next i
    
    IsValidFilename = valid
End Function

 

[alistercruickshanks]

Thank you very much everyone this is great and gives me a lot of direction to go on.

Really appreciate the advice.

 

[alistercruickshanks]

Since I've never been able to remember all patterns in regex, I do my validations with VBA.
Not as powerful as regex, but if it works, it works. I'm too lazy to learn what each of those numerous characters in regex stand for.

The following, Returns true if the passed text contains only space and alphanumerical and Hyphon.
Otherwise, it returns false. (text contains / * + $ # or others)

Public Function txtIsValid(TextToValidate) As Boolean

    txtIsValid = Not TextToValidate Like "*[!0-9 a-zA-Z]*" Or InStr(TextToValidate, "-")

End Function

If necessary, you can add other characters to the pattern too.
For example : "*[!0-9 .'a-zA-Z]*" returns false if the passed text contains any character outside the range of the following list:
alphanumerical , hyphen, Space, dot (.) and single quote (')

Thank you so much. This is exactly what I was looking for. I was very close at one point, but had the Not in the wrong place and why i was struggling.

This works perfect!

 

[DickyP]

Thank you so much. This is exactly what I was looking for. I was very close at one point, but had the Not in the wrong place and why i was struggling.

This works perfect!

If you used a RegEx you'd be able to check and remove the offending characters all in one command!

 

[Josef P.]

Note:

Public Function txtIsValid(TextToValidate) As Boolean<br><

   txtIsValid = Not TextToValidate Like "*[!0-9 a-zA-Z]*" Or InStr(TextToValidate, "-")
  
End Function

txtIsValid("!-?") => True
vs.

Public Function txtIsValid(TextToValidate) As Boolean<br><

   txtIsValid = Not TextToValidate Like "*[!0-9 a-zA-Z-]*"
  
End Function

 

[KitaYama]

Note:

Public Function txtIsValid(TextToValidate) As Boolean<br><

   txtIsValid = Not TextToValidate Like "*[!0-9 a-zA-Z]*" Or InStr(TextToValidate, "-")
 
End Function

txtIsValid("!-?") => True
vs.

Public Function txtIsValid(TextToValidate) As Boolean<br><

   txtIsValid = Not TextToValidate Like "*[!0-9 a-zA-Z-]*"
 
End Function

For my own database, I don't check for "-".
Since the OP needed to check for it too, I added it to the end with an OR.
Seems that it's making problem in this case.
Maybe I have to check for it first (in an IF statement).
Not in front of a PC to do some tests.

Thanks for noticing.

 

[Isaac]

and then the crudest, but easy to understand version is to loop through every character in plain vanilla vba. probably the grossest way, but a way nonetheless

Function IsValid(strInput As String) As Boolean
Dim x As Long

For x = 1 To Len(strInput)
    Select Case Mid(strInput, x, 1)
        Case "a", "b", "c" 'etc
        'do nothing
        Case Else
        IsValid = False
        Exit Function
    End Select
Next x

IsValid = True

End Function

Theoretically slow, but not actually slow if that makes sense.

 

[DickyP]

To repeat - RegEx is much simpler than unnecessary VBA code.

Examples below are the chars allowed in initial post but the Pattern is easily enhanced to add more characters.

To just check and return true if has unwanted chars.

Public Function HasNonChar(pstrIn As String) As Boolean
  
    Static RE As New VBScript_RegExp_55.RegExp
  
    RE.Pattern = "[^\x20\x27a-zA-Z]"
    HasNonChar = RE.Test(pstrIn)
End Function

or to delete them if preferred.

Public Function ReplaceNonChar(pstrIn As String) As String
  
    Static RE As New VBScript_RegExp_55.RegExp
  
    RE.Pattern = "[^\x20\x27a-zA-Z]"
    RE.Global = True
    ReplaceNonChar = RE.Replace(pstrIn, vbNullString)
End Function

The example code assumes early binding and the existence of the necessary reference. Easily altered to late binding if preferred.