Pop-up on conditions?

[dlovin123]

Posting for a co-worker. Thanks for the help.

-----

I want there to be a pop up window that comes up when the formula under the GP line is under 35% that tells you to write an explanation on the LOW GP line. So far, I’m able to get the color code system under GP working for me, and I could make a window pop up if I enter data directly and specifically in that certain cell. But that’s the problem. I don’t enter data under the GP line. I enter data under PO$, Labor$, and MAT COST. Everything else, Labor Cost, GP, and that last material line all auto populate because they have formulas.

 

[Snakehips]

dlovin123,

Here is code to be copied to the worksheet's code module.

It makes assumtions but may give you a start.

Private Sub Worksheet_Change(ByVal Target As Range)
'Assuming PO$ = column B Labor C Materials I
'so GP = column J
Select Case Target.Column
Case 2, 3, 9
r = Target.Row
'only display if all cells in the GP calc have a value
'otherwise it will fire on every entry
If Not Range("B" & r) = "" And Not Range("C" & r) = "" And Not Range("I" & r) = "" Then
If Range("J" & r) < 35 Then Resp = MsgBox("Please add an explanation as to why the GP is low", vbOKOnly, "LOW GP!!")
End If
Case Else
Exit Sub
End Select
End Sub

hOPE THAT HELPS.

 

[Jim885]

Here's my attempt.

Assuming the GP column is column J, enter this code in the worksheet module;

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count <> 1 Then Exit Sub
    If Not Intersect(Target, Range("J:J")) Is Nothing Then
        If Target.Value < 35 Then
             MsgBox "Enter explanation in the LOW GP column", , "Low GP!"
        End If
    End If
End Sub

You can also not include this line of code, as it is not necessary;

 If Target.Cells.Count <> 1 Then Exit Sub

 

[dlovin123]

I tried both of the codes and made the changes necessary, but I’m not getting anything to pop up.

For example:

Private Sub Worksheet_Change(ByVal Target As Range)
'Assuming PO$ = column N Labor O Materials U
'so GP = column V
Select Case Target.Column
Case 13, 14, 20
r = Target.Row
'only display if all cells in the GP calc have a value
'otherwise it will fire on every entry
If Not Range("N" & r) = "" And Not Range("O" & r) = "" And Not Range("U" & r) = "" Then
If Range("V" & r) < 0.35 Then Resp = MsgBox("Please add an explanation as to why the GP is low", vbOKOnly, "LOW GP!!")
End If
Case Else
Exit Sub
End Select
End Sub

AND

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("V:V")) Is Nothing Then
If Target.Value < 0.35 Then
MsgBox "Enter explanation in the LOW GP column", , "Low GP!"
End If
End If
End Sub



(I entered it with the < 35 and < 0.35 to see if that made a difference.)

Just to be clear, I activate the macro by pressing the “Run Sub/UserForm” button and then hitting run, correct? Or is there another step in order for the macro to stay on?

 

[Snakehips]

I tried both of the codes and made the changes necessary, but I’m not getting anything to pop up.

Just to be clear, I activate the macro by pressing the “Run Sub/UserForm” button and then hitting run, correct? Or is there another step in order for the macro to stay on?

No, Right click your sheet and copy the code, one or the other, into that VBA editor pane. Then the code will run, automatically, as and when your appropriate sheet values change. Both sets of code are a little different. Try both, separately, to decide which suits you best.

 

[Jim885]

I just noticed you want the macro to trigger on a formula rather than the cell being "Hard entered"

Here's the corect code that goes into the worksheet module;

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Application.Intersect(Target.Dependents, Range("J:J")) Is Nothing Then
        If Target.Value < 35 Then
             MsgBox "Enter explanation in the LOW GP column", , "Low GP!"
        End If
    End If
End Sub

 

[Jim885]

Never mind. As it is, my code works, but it also fires with every change in the worksheet. You don't want that.

It could be changed, but Snakehips has provided you with a better code that works with the conditions you want.

Today is not my day. Been at it too long.