Textbox search VBA

[JeremySun]

Below is the code I'm currently using the problem is it will only look for the uppercase of the word. This is in a Userform Textbox called reason. Basically what I would like to happen if the user types in any of these terms that will be on sheet1 column AA, a message box appears, if none of the words are present the contents are copied to the clipboard. Please Help.

Private Sub reason_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If (reason.Value) = "ACETAMINOPHEN/CODEINE" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "ADDERALL*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "ALPRAZOLAM*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "XANAX*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "BUTORPHANOL*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "STADOL*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "CARISOPRODOL*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "SOMA*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "CHLORAL HYDRATE*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "CHLORDIAZEPOXIDE*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "LIBRIUM*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "CLONAZEPAM*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "KLONOPIN*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "CLORAZEPATE*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "TRANXENE*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "CODEINE*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "DEXMETHYLPHENIDATE*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "FOCALIN*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "DEXTROAMPHETAMINE*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "DIAZEPAM*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "VALIUM*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "ESZOPICLONE*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "LUNESTA*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "FENTANYL*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "DURAGESIC*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "FIORINAL*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "GUAIFENESIN W/CODEINE*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "HYDROCODONE*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "HYDROMORPHONE*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "DILAUDID*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "LISDEXAMFETAMINE*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "VYNASE*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "LORAZEPAM*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "ATIVAN*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "LORTAB*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "MEPERIDINE*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "DEMEROL*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "METHADONE*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "METHYLPHENIDATE*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "CONCERTA*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "METHYLPHENIDATE*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "DAYTRANA*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "METHYLPHENIDATE*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "RITALIN*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "MODAFINIL*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "PROVIGIL*" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "MORPHINE" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "OXAZEPAM" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "SERAX" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "OXYCODONE" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "PERCOCET" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "PHENOBARBITAL" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "PREGABALIN" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "LYRICA" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "SUBOXONE" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "TEMAZEPAM" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "RESTORIL" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "TRAMADOL" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "ULTRAM" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "TRIAZOLAM" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "HALCION" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "ZOLPIDEM" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
If (reason.Value) = "AMBIEN" Then MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
End Sub

 

[Jonmo1]

Try adding Ucase to the reason.value

If Ucase(reason.Value) = "ACETAMINOPHEN/CODEINE" Then

 

[Jonmo1]

Also, since the message box is the same for all instances..

Instead of 1 line for each possible medication, try like this..

Dim MyVals As Variant
Dim i As Long
 
'Enter list of medications here
MyVals = Array("ACETAMINOPHEN/CODEINE", "ADDERALL*", "ALPRAZOLAM*","etc")
 
For i = LBound(MyVals) To UBound(MyVals)
    If UCase(reason.Value) Like MyVals(i) Then
        MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
        Exit For
    End If
Next i

 

[JeremySun]

It works a little better but if the user types anything beside the medication then it will not generate the msgbox, any idea?

 

[Jonmo1]

It works a little better but if the user types anything beside the medication then it will not generate the msgbox, any idea?

Isn't that the idea?

The message box should only appear if one of the pre determined medications is entered.

 

[JeremySun]

If the user type for example: Please refill patients ambien 10mg.

The word ambien is on the list, but since there is text prior and after the vba doesn't see it.

 

[Jonmo1]

Put * before and after each medication in the list...

"*ACETAMINOPHEN/CODEINE*"

or this is probably easier

If UCase(reason.Value) Like "*" & MyVals(i) & "*" Then

Then no need for the * in each medication in the list.

 

[pbornemeier]

This should copy anything that was not in the list to the clipboard:

Private Sub reason_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim MyVals As Variant
    Dim i As Long
    Dim bFound As Boolean
    Dim objMyData As New DataObject
    'In VBE use Tools | References to add reference
    '"Microsoft Forms 2.0" (FM20.DLL) (or later version)
 
    'Enter list of medications here
    MyVals = Array("ACETAMINOPHEN/CODEINE", "ADDERALL*", "ALPRAZOLAM*", "etc")
 
    For i = LBound(MyVals) To UBound(MyVals)
        If UCase(reason.Value) Like MyVals(i) Then
            MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
            bFound = True
            Exit For
        End If
    Next i
 
    If Not bFound Then
        objMyData.SetText reason.Value
        objMyData.PutInClipboard
    End If
End Sub

You will need to add a reference to MS Forms in the VBE Tools | References

Have you considered using a combobox - that would take care of any misspellings

 

[JeremySun]

Thank you all this works. Now is there a way to make it highlight any of the MyVals if they are in the textbox?

Private Sub check_Click()
    Dim MyVals As Variant
    Dim i As Long
    Dim bFound As Boolean
    Dim objMyData As New DataObject
    'In VBE use Tools | References to add reference
    '"Microsoft Forms 2.0" (FM20.DLL) (or later version)
 
    'Enter list of medications here
    MyVals = Array("ACETAMINOPHEN/CODEINE", "ADDERALL", "ALPRAZOLAM ", "XANAX", "BUTORPHANOL", " STADOL", "CARISOPRODOL", "SOMA", "CHLORAL HYDRATE", "CHLORDIAZEPOXIDE", "LIBRIUM", "CLONAZEPAM", "KLONOPIN", "CLORAZEPATE", "TRANXENE", "CODEINE", "DEXMETHYLPHENIDATE", "FOCALIN", "DEXTROAMPHETAMINE", "DIAZEPAM", "VALIUM", "ESZOPICLONE", "LUNESTA", "FENTANYL", "DURAGESIC", "FIORINAL", "GUAIFENESIN W/CODEINE", "HYDROCODONE", "HYDROMORPHONE", "DILAUDID", "LISDEXAMFETAMINE", "VYNASE", "LORAZEPAM", "ATIVAN", "LORTAB", "MEPERIDINE", "DEMEROL", "METHADONE", "METHYLPHENIDATE", "CONCERTA", "METHYLPHENIDATE", "DAYTRANA", "METHYLPHENIDATE", "RITALIN", "MODAFINIL", "PROVIGIL", "MORPHINE", "OXAZEPAM", "SERAX", "OXYCODONE", "PERCOCET", "PHENOBARBITAL", "PREGABALIN", "LYRICA", "SUBOXONE", "TEMAZEPAM", "RESTORIL", "TRAMADOL", "ULTRAM", "TRIAZOLAM", "HALCION", "ZOLPIDEM", "AMBIEN")
 
    For i = LBound(MyVals) To UBound(MyVals)
        If UCase(reason.Value) Like "*" & MyVals(i) & "*" Then
            MsgBox ("Please notify patient that the medication they are asking for may require them to have an appointment. Please check with local SOP to ensure the medication can be asked for via Telephone Consult")
            bFound = True
            
            Exit For
        End If
    Next i
    
            If Not bFound Then
        objMyData.SetText reason.Value
        objMyData.PutInClipboard
        MsgBox ("Information Copied to Clipboard, just paste into AHLTA")
    End If
      End Sub

 

[pbornemeier]

As far as I know, you cannot format part of the text in a text box (although you can make changes to all of it). The following code will capitalize the first instance of each of the names in the array that appear in the text box and add line breaks so the names appear on separate lines.

Put this snippet just ahead of the End Sub

    For i = LBound(MyVals) To UBound(MyVals)
        lPos = InStr(UCase(reason.Value), MyVals(i))
        lLen = Len(MyVals(i))
        If lPos > 0 Then
            reason.Value = Left(reason.Value, lPos - 1) & vbLf & UCase(Mid(reason.Value, lPos, lLen)) & vbLf & Right(reason.Value, Len(reason.Value) - lPos - lLen + 1)
        End If
    Next i

You should probably remove the space before STADOL in the array.