VBA warning message when specific drop down selection is made

Shadkng

Active Member
Joined
Oct 11, 2018
Messages
365
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.

Code:
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
 
Upvote 0
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.

Code:
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"
 
Upvote 0
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.

Code:
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
 
Upvote 0
Try changing

Code:
If Target = "item5" Then
to
Code:
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
 
Upvote 0
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
 
Upvote 0
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.
Code:
If UCase(Target) Like "GAL*" Or UCase(Target) Like "POP*" Then

or

Code:
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
 
Last edited:
Upvote 0
So I can several different warnings in one sheet with the if statement and then I would change the target cell in "hideme" ?
 
Upvote 0
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top