Solved

Excel Message Box dependent on cell value

Posted on 2010-08-26
12
375 Views
Last Modified: 2012-08-13
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
Comment
Question by:Unreal1998
  • 3
  • 3
  • 2
  • +3
12 Comments
 
LVL 4

Accepted Solution

by:
javaftper earned 100 total points
ID: 33535912
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
 
LVL 4

Expert Comment

by:javaftper
ID: 33535949
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
 
LVL 17

Assisted Solution

by:calacuccia
calacuccia earned 100 total points
ID: 33535979
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
 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 100 total points
ID: 33535982
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 33535997
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
 
LVL 8

Assisted Solution

by:2toria
2toria earned 100 total points
ID: 33536002
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 18

Expert Comment

by:Cluskitt
ID: 33536008
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 33536012
Other event my friend :-)
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33536031
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
 
LVL 8

Expert Comment

by:2toria
ID: 33536062
Oops..  Misread the question and went off and did my own thing.  Ignore.

Matt
0
 
LVL 6

Assisted Solution

by:sijpie
sijpie earned 100 total points
ID: 33556441
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
 

Author Closing Comment

by:Unreal1998
ID: 33570777
Great! Too bad I could not give 500 points to all.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now