Vba code to check if a string contains special symbols

[kelly mort]

Hi,

I have been searching for the solution to this for a while now but I can't seem to get the right solution.

How do I check for the availability of symbols such as, @,!,@, and so on?

That's any character that's not a number or letter.

I found this on the Web.

Sub Password()

Dim b As Integer
Dim i As Integer, j As Integer, k As Integer
Dim psw As String

Dim hasNum As Boolean, hasUpper As Boolean, hasLower As Boolean

Dim LengthOFPasswordsList As Long

LengthOFPasswordsList = Range("D" & Rows.Count).End(xlUp).Row

For b = 3 To LengthOFPasswordsList
    'assume the password is no good.
    hasNum = False
    hasUpper = False
    hasLower = False

    'capture the psw in question
    psw = Range("D" & b)

    'see if there is a number in the password
    'NOTE: the following For loops uses the ASCII values for numbers and letters.
    For k = 48 To 57
        If (InStr(1, psw, Chr(k))) Then
            hasNum = True
            Exit For
        End If
    Next k

    'See if there is an upper case
    For i = 65 To 90
        If (InStr(1, psw, Chr(i))) Then
            hasUpper = True
            Exit For
        End If
    Next i

    'See if there is a lower case
    For j = 97 To 122
        If (InStr(1, psw, Chr(j))) Then
            hasLower = True
            Exit For
        End If
    Next j

    'See if all criteria was met
    If Not hasLower Or Not hasUpper Or Not hasNum Or (Len(psw) <> 8) Then
        Range("F" & b) = "Password Inválida"
    End If
Next b

End Sub

As the above code is check for caps, lower case and numbers, I need another criteria to check for special characters or symbols.
Thanks in advance
Kelly Mort.

 

[kelly mort]

Sorry for not replying early. My network went down:

Sub Password()

Dim b As Integer
Dim i As Integer, j As Integer, k As Integer
Dim psw As String

Dim hasNum As Boolean, hasUpper As Boolean, hasLower As Boolean
    'assume the password is no good.
    hasNum = False
    hasUpper = False
    hasLower = False

    'capture the psw in question
    psw = TextBox1.Text

    For k = 48 To 57
        If (InStr(1, psw, Chr(k))) Then
            hasNum = True
            Exit For
        End If
    Next k

    'See if there is an upper case
    For i = 65 To 90
        If (InStr(1, psw, Chr(i))) Then
            hasUpper = True
            Exit For
        End If
    Next i

    'See if there is a lower case
    For j = 97 To 122
        If (InStr(1, psw, Chr(j))) Then
            hasLower = True
            Exit For
        End If
    Next j

    'See if all criteria was met
    If Not hasLower Or Not hasUpper Or Not hasNum Or (Len(psw) <> 8) Then
    
    Else 

    End If

End Sub

This is how I am implementing the code atm.

 

[Rick Rothstein]

Okay, let's see if I understand your requirement. You need an 8 character text string composed of only letters and digits and that text must contain at least on upper case letter, at least one lower case letter and at least one digit, is that correct? If so, here is a function that will return either True or False depending if the text passed into it meets that condition or not...

Function IsLettersOrDigits(Txt As String) As Boolean
  IsLettersOrDigits = (Not Txt Like "*[!A-Za-z0-9]*") And (Txt Like "*[A-Z]*") And (Txt Like "*[a-z]*") And (Txt Like "*#*") And (Len(Txt) = 8)
End Function

Since you want to check the contents of TextBox1, you would call this function inside your macro something like this...

Sub Passwords()
  If IsLettersOrDigits(TextBox1.Text) Then
    MsgBox "TextBox1 meets all of the required conditions"
  Else
    MsgBox "TextBox1 does NOT meet the required criteria"
  End If
End Sub

 

[kelly mort]

Check a range (G column in this example) and highlight cell when special character is found:

Sub CheckRangeForSpecialCharactersWithOutRegEx()
'
    Dim r As Range, rng As Range, s As String
    Dim i As Long, L As Long
'
    Set rng = Intersect(Range("G:G"), ActiveSheet.UsedRange)
'
    For Each r In rng
        If r.Value <> "" Then
            s = Replace(r.Text, "-", "")
            L = Len(s)
            For i = 1 To L
                If Not Mid(s, i, 1) Like "[0-9a-zA-Z]" Then
                    r.Interior.Color = vbYellow
                End If
            Next i
        End If
    Next r
End Sub

@johnnyL

Your code solved the problem.

Have a wonderful time.

 

[kelly mort]

Okay, let's see if I understand your requirement. You need an 8 character text string composed of only letters and digits and that text must contain at least on upper case letter, at least one lower case letter and at least one digit, is that correct? If so, here is a function that will return either True or False depending if the text passed into it meets that condition or not...

Function IsLettersOrDigits(Txt As String) As Boolean
  IsLettersOrDigits = (Not Txt Like "*[!A-Za-z0-9]*") And (Txt Like "*[A-Z]*") And (Txt Like "*[a-z]*") And (Txt Like "*#*") And (Len(Txt) = 8)
End Function

Since you want to check the contents of TextBox1, you would call this function inside your macro something like this...

Sub Passwords()
  If IsLettersOrDigits(TextBox1.Text) Then
    MsgBox "TextBox1 meets all of the required conditions"
  Else
    MsgBox "TextBox1 does NOT meet the required criteria"
  End If
End Sub

@Rick Rothstein

This version of your code also worked for me. Thanks for the time

 

[johnnyL]

Thank you @kelly mort, I think @Rick Rothstein wins the green flag for this one.