VBA warning message when specific drop down selection is made
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: VBA warning message when specific drop down selection is made
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2018
    Posts
    209
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default VBA warning message when specific drop down selection is made

    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

  2. #2
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,514
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA warning message when specific drop down selection is made

    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
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  3. #3
    Board Regular
    Join Date
    Jan 2014
    Location
    Dublin, Ireland
    Posts
    1,421
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA warning message when specific drop down selection is made

    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"
    Learn something new everyday.

    be sure to use code tags

    Code:
    [ code ]
    [ / code ]
    ' no spaces

  4. #4
    Board Regular
    Join Date
    Oct 2018
    Posts
    209
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA warning message when specific drop down selection is made

    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


    Quote Originally Posted by Scott T View Post
    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

  5. #5
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,514
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA warning message when specific drop down selection is made

    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
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  6. #6
    Board Regular
    Join Date
    Oct 2018
    Posts
    209
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA warning message when specific drop down selection is made

    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

  7. #7
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,514
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA warning message when specific drop down selection is made

    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 by Scott T; Feb 4th, 2019 at 01:39 PM.
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  8. #8
    Board Regular
    Join Date
    Oct 2018
    Posts
    209
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA warning message when specific drop down selection is made

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

  9. #9
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,514
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA warning message when specific drop down selection is made

    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
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  10. #10
    Board Regular
    Join Date
    Oct 2018
    Posts
    209
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA warning message when specific drop down selection is made

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •