VBA warning message when specific drop down selection is made

Shadkng

Board Regular
Joined
Oct 11, 2018
Messages
210
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,612
Office Version
365, 2016
Platform
Windows
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
 

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,421
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"
 

Shadkng

Board Regular
Joined
Oct 11, 2018
Messages
210
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
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,612
Office Version
365, 2016
Platform
Windows
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
 

Shadkng

Board Regular
Joined
Oct 11, 2018
Messages
210
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

Well-known Member
Joined
Dec 14, 2016
Messages
2,612
Office Version
365, 2016
Platform
Windows
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:

Shadkng

Board Regular
Joined
Oct 11, 2018
Messages
210
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

Well-known Member
Joined
Dec 14, 2016
Messages
2,612
Office Version
365, 2016
Platform
Windows
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
 

Shadkng

Board Regular
Joined
Oct 11, 2018
Messages
210
Thanks I will experiment with this method. I'll let you know how it works.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,814
Messages
5,483,073
Members
407,376
Latest member
stan vacek

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top