asked on

Phrase Not Like won't work in VBA If statement

I have a field on an Excel 2003 form that needs to be checked to be sure the user entered, as part of the code name, the terms either COUNTRY or CURRENCY. However, I can't seem to get the If statement to evaluate the Not Like phrase.

'First check to be sure Portfolio Code has the term of either COUNTRY or CURRENCY in it.
If Me.txtPortfolioCode.text Not Like "*COUNTRY*" Or Me.txtPortfolioCode.text  Not Like "*CURRENCY*" Then
    MsgBox "You have not indicated whether this is a COUNTRY or CURRENCY " & vbCrLf & _
                  "for the Worksheet Tab Name.  Please correct naming convention and then save data.", vbOKOnly
                  Me.txtPortfolioCode.SetFocus
                  Exit Sub
    End If

Open in new window

Rory Archibald

If Not Me.txtPortfolioCode.text Like "*COUNTRY*" And Not Me.txtPortfolioCode.text  Like "*CURRENCY*" Then

Open in new window

for example.

zorvek (Kevin Jones)

If Not Me.txtPortfolioCode.text Like "*COUNTRY*" Or Not Me.txtPortfolioCode.text Like "*CURRENCY*" Then

Kevin

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sandra Smith

ASKER

Change to below and still does not recognize the phrase. I tried using both text and value

If Not Me.txtPortfolioCode.Value Like "*COUNTRY*" Or Not Me.txtPortfolioCode.text Like "*CURRENCY*" Then
MsgBox "You have not indicated whether this is a COUNTRY or CURRENCY " & vbCrLf & _
"for the Worksheet Tab Name. Please correct naming convention and then save data.", vbOKOnly
Me.txtPortfolioCode.SetFocus
Exit Sub
End If

If Not Me.txtPortfolioCode.text Like "*COUNTRY*" Or Not Me.txtPortfolioCode.text Like "*CURRENCY*" Then
MsgBox "You have not indicated whether this is a COUNTRY or CURRENCY " & vbCrLf & _
"for the Worksheet Tab Name. Please correct naming convention and then save data.", vbOKOnly
Me.txtPortfolioCode.SetFocus
Exit Sub
End If

Rory Archibald

I repeat: And not Or, as I put in my code. ;)

Sandra Smith

ASKER

That was it, using AND rather than OR. Thank you.

Sandra

Sandra Smith

ASKER

Sorry rorya, I did not notice the difference at first so missed the AND usage. Also, my screen did not refresh so missed your added comments referencing this oversight. Thank you again for the advice.

Sandra

Rory Archibald

No worries. Glad to help. :)