VBA warning message when specific drop down selection is made

[Shadkng]

Hi,would the following be possible with VBA...I would like a warning message to pop up when a particular selection is made from a drop down list in a worksheet, or in a specific range if it must. However, I want the message to pop up only the first time that selection is made, so suppressed after that. Would the selection have to be exact or could it be based on a string or prefix of the name in the selection? I would not want the message to pop up if that selection is existing when opening a saved workbook. This is not a super important thing so I wouldn't want you to put a lot of effort if needed. Thanks

 

[Scott T]

Try
Create a worksheet you can hide it I named it hideme to hold if the message has been displayed.
This should be put on the sheet that has the drop down list. I assumed it was A1 change as need.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing And Sheets("hideme").Range("A1") <> 1 Then
    If Target = "item5" Then
        MsgBox "your message here"
        Sheets("hideme").Range("A1") = 1
    End If
End If
End Sub

 

[BarryL]

The below could work, give it a try in a test version of your workbook. Select the sheet you want this check on, right click on the name of the sheet at the bottom, press View Code and paste the below in the worksheet object.

Public counter As Long


Private Sub Worksheet_Activate()
Dim nc As Range
Set nc = Cells.Find(What:="Warning", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
If Not nc Is Nothing Then counter = 1
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim MB As Long


If counter = 1 Then Exit Sub


If Target.Value = "Warning" Then ' if you want to this to be a "contains" rather than an "equals" add a * sign before and after the text
MB = MsgBox("Are you sure? You just activated a warning", vbYesNo)
End If
If MB = vbNo Then Target.Value = ""
counter = 1
End Sub

Whatever the value is that you want to check, replace it with where you see "Warning"

 

[Shadkng]

Hi, this so far works well, however how can I use wildcards so my target can be a string or a few letters. For example items "GAL-G200", "GAL-G400", so use "GAL". And can I put multiple target items? Thanks

Try
Create a worksheet you can hide it I named it hideme to hold if the message has been displayed.
This should be put on the sheet that has the drop down list. I assumed it was A1 change as need.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing And Sheets("hideme").Range("A1") <> 1 Then
    If Target = "item5" Then
        MsgBox "your message here"
        Sheets("hideme").Range("A1") = 1
    End If
End If
End Sub

 

[Scott T]

Try changing

If Target = "item5" Then

to

If UCase(Target) Like "GAL*" Then

Note this will only display the message the first time any GAL* item is select not once for each item

 

[Shadkng]

Ok that change worked well. How about having more than one "like", so "GAL" or "SL" for example. Also is there a method to denote a string of characters in the target? Thanks

 

[Scott T]

you could use or to add more items. If you have a lot then you may want to use a Select Case instead of IF.

If UCase(Target) Like "GAL*" Or UCase(Target) Like "POP*" Then

or

Select Case True
        Case (UCase(Target) Like "GAL*")
            MsgBox "your message here"
            Sheets("hideme").Range("A1") = 1
        Case (UCase(Target) Like "POP*")
            MsgBox "your other message here"
            Sheets("hideme").Range("A1") = 1
    
    End Select

 

[Shadkng]

So I can several different warnings in one sheet with the if statement and then I would change the target cell in "hideme" ?

 

[Scott T]

Yes you could do that but you would also have to check the different cells to see if it had already run or not. This could also be done with multiple if statements or else if.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
    Select Case True
        Case (UCase(Target) Like "GAL*") And Sheets("hideme").Range("A1") <> 1
            MsgBox "your message here"
            Sheets("hideme").Range("A1") = 1
        Case (UCase(Target) Like "POP*") And Sheets("hideme").Range("A2") <> 1
            MsgBox "your other message here"
            Sheets("hideme").Range("A2") = 1
    
    End Select
    
End If
End Sub

 

[Shadkng]

Thanks I will experiment with this method. I'll let you know how it works.