VBA for message box targeting a cell and specific word (string) selection

[dwarnimont]

Asking for help to create VBA code for a message box.

Objective: A targeted cell, using a list dropdown with several options posts a message if a specific string is selected from the list

Part 1: If a list dropdown option is selected (change) containing a specific text string (or value?), a messagebox will popup with a specific message.
Part 2: the yes/no, if/else branch. I have the second part of the code , but I don’t know how to create the first part.

Sub SayHello()
Msg = "message to user "
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbNo Then
MsgBox "Oh, never mind."
Else
MsgBox "I must be clairvoyant!"
End If
End Sub


Thanks,
David

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim msg As String
   
   msg = "message to user "

   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("[COLOR=#ff0000]F7[/COLOR]")) Is Nothing Then
      If LCase(Target.Value) = "hi" Then
         If MsgBox(msg, vbYesNo) = vbNo Then
            MsgBox "Oh, never mind."
         Else
            MsgBox "I must be clairvoyant!"
         End If
      End If
   End If
End Sub

This needs to go in a sheet module.
Right click the tab with your dropdown > select view code > paste the code into the window that opens.
Change the range in red to suit

 

[dwarnimont]

This isnt working by use within a list dropdown. it's odd. if you overwrite the list cell selection it does work. Did you try? Thoughts? Thanks for your help!!!!

 

[Fluff]

When you say a "dropdown" are you talking about a Data Validation dropdown, or something else?

 

[dwarnimont]

If found the cause of failure LCase. my list include Caps. why was this included? Can you simply remove it?

 

[Fluff]

As long as the string being checked in the code is all lower case (in my example "hi") then it will work regardless of whether the cell is hi, Hi, HI or even hI.