• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

Excel Message Box dependent on cell value

I have a list on cell B3 and I want a message to appear every time a certain value is selected. For example:

On cell value change

 IF B3="JUSTIN B" then show message "RUN FOR YOUR LIFE!"

Lastly, can I duplicate this macro for multiple cell values and show other messages.

For example:

 IF B3="JUSTIN B" then show message "RUN FOR YOUR LIFE!"

 IF B4="Y" then show message "Complete Question on Cell G7!" And so forth...
0
Unreal1998
Asked:
Unreal1998
  • 3
  • 3
  • 2
  • +3
5 Solutions
 
javaftperCommented:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("B3").Value = "JUSTIN B" Then
    MsgBox "RUN FOR YOUR LIFE!"
End If

End Sub
0
 
javaftperCommented:
You can add as many IF statements as you like, eg-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("B3").Value = "JUSTIN B" Then
    MsgBox "RUN FOR YOUR LIFE!"
End If

If Range("B4").Value = "Y" Then
    MsgBox "Complete Question on Cell G7!"
End If

End Sub
0
 
calacucciaCommented:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("B3")) Is Nothing Then
    If Me.Range("B3") = "JUSTIN B" Then
        MsgBox "Run for your life"
    End If
ElseIf Not Intersect(Target, Me.Range("B4")) Is Nothing Then
    If Me.Range("B4") = "Y" Then
        MsgBox "Complete question on Cell G7!"
    End If
ElseIf Not Intersect(Target, Me.Range("B5")) Is Nothing Then

ElseIf Not Intersect(Target, Me.Range("B6")) Is Nothing Then

End If

End Sub
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
CluskittCommented:
Actually, that will spawn a MsgBox even when another cell is changed.
Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Address="$B$3" And Range("B3").Value="JUSTIN B" Then
    MsgBox "RUN FOR YOUR LIFE!"
  ElseIf Target.Address="$B$4" And Range("B4").Value="Y" Then
    MsgBox "Complete Question on Cell G7!"
  End If
End Sub
0
 
calacucciaCommented:
It's not the SelectionChange but Change event which you need, otherwise the test and message will pop up every time any cell is selected.

In my code, it is also checked whether one of the trigger cell is changed, a necessary condition.

In my example, I have showed 2 empty conditions, for you to play around and extend.
0
 
2toriaCommented:
Another option (if you have plenty of different criteria you want to run with and want to reduce your code) would be to use SELECT CASE, like this:-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Range("B3").value
     Case "JUSTIN B"
         MsgBox "RUN FOR YOUR LIFE!"
     Case "Y"
         MsgBox "Complete Question on cell G7"
     Case "Whatever you want here"
         Msgbox "Whatever you want to say"

---repeat as necessary, then..

     Case Else
          'Do nothing here
End Select

End Sub
0
 
CluskittCommented:
The MsgBox spawning message was intended for javatper's code, not calacuccia's. Calacuccia's seems to be pretty much like mine, only I'd like to think that my If statements are prettier :D
0
 
calacucciaCommented:
Other event my friend :-)
0
 
CluskittCommented:
Actually, I wanted Worksheet_Change too, cause that's what makes sense. I had assumed javatper's was that one and I copied/pasted without noticing :P
0
 
2toriaCommented:
Oops..  Misread the question and went off and did my own thing.  Ignore.

Matt
0
 
sijpieCommented:
Unreal1998, you want a moregeneric code then the ones given above. The cde needs to b written in he module of the sheet: righclick on the tab of the sheet and select: View code...

In the window that opens add the following, and modify / add ranges to be checked as indicated:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Message As String
    If Not Intersect(Target, Range("B3", "B4", [list your cells to be checked here])) Is Nothing Then
        Select Case Target.Address
            Case "$B$3"
                Select Case Target.Value
                    Case "JUSTIN B"
                        Message = "Run for your life!"
                    Case "Something Else"
                        Message = "Another response"
                End Select
            Case "$B$4"
                 Select Case Target.Value
                    Case "Y"
                        Message = "Complete question in G7"
                    Case "N"
                        Message = "Complete question in G8"
                    Case "Something Else"
                        Message = "Another response"
                End Select
            Case "your next range to be checked"
                 Select Case Target.Value
                    Case "Etc"
                        Message = "etc"
                End Select
        End Select
        If Message <> vbNullString Then MsgBox Message
    End If
End Sub

Open in new window

0
 
Unreal1998Author Commented:
Great! Too bad I could not give 500 points to all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now