VBA Code for Pop Up

[Tennisguuy]

I am trying to create a pop up window with VBA code. I am not good with VBA code but found some things on line but it's not working. I want to have a pop up window display a message once if the value in F7 equals a certain value and if possible have it only popup again if the value in F7 changes and meet another criteria.

This is the code I have but it not working.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F7:F7")) Is Nothing And _
If Range("F7").Value =1 Then MsgBox "RISK SCORE OF 1 IS 20% OR MORE LOWER THAN YOUR AVERAGE RISK"
If Range("F7").Value =2 Then MsgBox "RISK SCORE OF 1 IS 20% OR MORE LOWER THAN YOUR AVERAGE RISK"
If Range("F7").Value =3 Then MsgBox "RISK SCORE OF 1 IS 20% OR MORE LOWER THAN YOUR AVERAGE RISK"
If Range("F7").Value =4 Then MsgBox "RISK SCORE OF 1 IS 20% OR MORE LOWER THAN YOUR AVERAGE RISK"
If Range("F7").Value =5 Then MsgBox "A RISK SCORE OF 5 IS TYPICALLY 0% TO 5% LOWER THAN YOUR AVERAGE RISK IN YOUR DATA"
If Range("F7").Value =6 Then MsgBox "RISK SCORE OF 6 IS 0% TO 7.5% MORE HIGHER THAN YOUR AVERAGE RISK"
If Range("F7").Value =7 Then MsgBox "RISK SCORE OF 7 IS 7.5% TO 15% MORE HIGHER THAN YOUR AVERAGE RISK"
If Range("F7").Value =8 Then MsgBox "RISK SCORE OF 8 IS 15% TO 25% MORE HIGHER THAN YOUR AVERAGE RISK"
If Range("F7").Value =9 Then MsgBox "RISK SCORE OF 9 IS 20% TO 25% MORE HIGHER THAN YOUR AVERAGE RISK"
If Range("F7").Value =10 Then MsgBox "RISK SCORE OF 10 IS 25% TO 30% MORE HIGHER THAN YOUR AVERAGE RISK"
End If
End Sub

 

[Fluff]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F7:F7")) Is Nothing [COLOR=#0000ff]Then[/COLOR]
   If Range("F7").Value = 1 Then MsgBox "RISK SCORE OF 1 IS 20% OR MORE LOWER THAN YOUR AVERAGE RISK"
   If Range("F7").Value = 2 Then MsgBox "RISK SCORE OF 1 IS 20% OR MORE LOWER THAN YOUR AVERAGE RISK"
   If Range("F7").Value = 3 Then MsgBox "RISK SCORE OF 1 IS 20% OR MORE LOWER THAN YOUR AVERAGE RISK"
   If Range("F7").Value = 4 Then MsgBox "RISK SCORE OF 1 IS 20% OR MORE LOWER THAN YOUR AVERAGE RISK"
   If Range("F7").Value = 5 Then MsgBox "A RISK SCORE OF 5 IS TYPICALLY 0% TO 5% LOWER THAN YOUR AVERAGE RISK IN YOUR DATA"
   If Range("F7").Value = 6 Then MsgBox "RISK SCORE OF 6 IS 0% TO 7.5% MORE HIGHER THAN YOUR AVERAGE RISK"
   If Range("F7").Value = 7 Then MsgBox "RISK SCORE OF 7 IS 7.5% TO 15% MORE HIGHER THAN YOUR AVERAGE RISK"
   If Range("F7").Value = 8 Then MsgBox "RISK SCORE OF 8 IS 15% TO 25% MORE HIGHER THAN YOUR AVERAGE RISK"
   If Range("F7").Value = 9 Then MsgBox "RISK SCORE OF 9 IS 20% TO 25% MORE HIGHER THAN YOUR AVERAGE RISK"
   If Range("F7").Value = 10 Then MsgBox "RISK SCORE OF 10 IS 25% TO 30% MORE HIGHER THAN YOUR AVERAGE RISK"
End If
End Sub

 

[Joe4]

How is the value in cell F7 being updated?
Is it a value or a formula?
If a formula, what is that formula?

A Worksheet_Change is only triggered by a manual update. The way you have written it, the code would only be called when a manual update is being made to cell F7.
If F7 is a formula, you would want the VBA code to be triggered when a change happens to the cells used in the formula (and not F7 itself).

Also, I would suggest using a CASE statement instead of 10 IF statements like that. Using 10 IF statements structured like that is inefficient, because it is going to evaluate all 10 every time. So, if the first condition is met, it is still going through the other nine.

See here for how to write a CASE statement: https://www.techonthenet.com/excel/formulas/case.php

 

[Yongle]

or try this method
- separate Case 1 to 4 if you want a different message for each one

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("F7")) Is Nothing Then MsgBox GetMessage(Target)
End Sub

Private Function GetMessage(ByVal v As Integer) As String
Dim msg As String
Select Case v
    Case 1, 2, 3, 4:    msg = "RISK SCORE OF 1 IS 20% OR MORE LOWER THAN YOUR AVERAGE RISK"
    Case 5: msg = "A RISK SCORE OF 5 IS TYPICALLY 0% TO 5% LOWER THAN YOUR AVERAGE RISK IN YOUR DATA"
    Case 6: msg = "RISK SCORE OF 6 IS 0% TO 7.5% MORE HIGHER THAN YOUR AVERAGE RISK"""
    Case 7: msg = "RISK SCORE OF 7 IS 7.5% TO 15% MORE HIGHER THAN YOUR AVERAGE RISK"
    Case 8: msg = "RISK SCORE OF 8 IS 15% TO 25% MORE HIGHER THAN YOUR AVERAGE RISK"
    Case 9: msg = "RISK SCORE OF 9 IS 20% TO 25% MORE HIGHER THAN YOUR AVERAGE RISK"
    Case 10: msg = "RISK SCORE OF 10 IS 25% TO 30% MORE HIGHER THAN YOUR AVERAGE RISK"
End Select
    GetMessage = msg
End Function

The value in F7 is passed to the function
The message string is determined by that value
Select case is used because it is less climsy
The function returns the appropriate meassage
A message box returns the value returned by the function

That

 

[Tennisguuy]

Fluff I copied your code and pasted in the module and nothing no pop up box when I enter any value of 1-10

Joe, the value in cell F7 is entered directly each time. A formula is not used to input the value

 

[Fluff]

Is the code in the sheet module? and did you remove the existing code?

 

[Tennisguuy]

Yes I removed the existing code and copied your code in the sheet module. I created the module by going into the developer tab clicking on visual basic, then went to the insert menu and selected module. Was that correct

 

[Joe4]

I created the module by going into the developer tab clicking on visual basic, then went to the insert menu and selected module. Was that correct

No, you do not create Sheet modules. They already exist.
Event procedure code MUST go in the pre-created Sheet and Workbook modules in order to work correctly.

One easy way to get there is to go to the sheet you want to apply this to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and it will open the VB Editor up to the Module that you need to paste your code in.

 

[Tennisguuy]

Ok that worked. Right-clicking on the sheet and pasting the code. Thanks you everyone for your help. Much much appreciated.

 

[Tennisguuy]

Oh forgot one other question, Joe if I wanted to add code for another cell on the sheet how do you add another module