how to specify Pop up settings?

[peter85]

Today I made pop-ups in my sheet, the only irritating thing is that they pop-up each time a cell in the worksheet is changed..
this is the code I used:



Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Pop Up").Activate
Set Target = Range("C4")
If Target = "Shortage" Then
MsgBox "Attention! Shortage on Brackets, Inform Supervisor Immediately!"
End If


Worksheets("Pop Up").Activate
Set Target = Range("C5")
If Target = "Shortage" Then
MsgBox "Attention! Shortage on P/UP Cable, Inform Supervisor Immediately!"
End If

Worksheets("Pop Up").Activate
Set Target = Range("C6")
If Target = "Shortage" Then
MsgBox "Attention! Shortage on Skew Screw, Inform Supervisor Immediately!"
End If


End Sub

How can I change it this way, I only want the pop-up to occur when the value in cells C4,C5 or C6 show "Shortage"..
I also want the pop-up to show up when the workbook is opened..

 

[phxsportz]

Dim popup As Boolean

If Range("c4") = "Shortage" Then
   popup = True
ElseIf Range("c5") = "Shortage" Then
   popup = True
ElseIf Range("c6") = "Shortage" Then
   popup = True
Else
   popup = False
End If
If popup Then
   MsgBox "Attention! Shortage on Brackets, Inform Supervisor Immediately!", vbOKOnly
End If

 

[peter85]

Thanks for your efforts, now this is the formula I have:

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Pop Up").Activate
Dim popup As Boolean

If Range("c4") = "Shortage"
popup = True
elseIf Range("c5") = "Shortage"
popup = True
elseIf Range("c6") = "Shortage"
popup = True
Else
popup = False
End If
if popup
MsgBox "Attention! Shortage on Brackets, Inform Supervisor Immediately!", vbOKOnly
End If


End Sub

however I still get the same error, suggestions?

 

[phxsportz]

Yes... the "THEN" statement is missing from the If/ELSEIF lines..

Cut and paste the code directly (I had made the change after you got it)

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Pop Up").Activate
Dim popup As Boolean

If Range("c4").Value = "Shortage" Then
popup = True
ElseIf Range("c5").Value = "Shortage" Then
popup = True
ElseIf Range("c6").Value = "Shortage" Then
popup = True
Else
popup = False
End If
If popup Then
MsgBox "Attention! Shortage on Brackets, Inform Supervisor Immediately!", vbOKOnly
End If
End Sub

P.S. I tested Norie's code and it worked perfectly. Note that it is case-sensitive if you enter shortage it won't trigger a MsgBox.

 

[peter85]

When I use this code, I still get the msgbox with each file in the sheet I change, however I only get the msgbox from the first shortage, not from the other 2 anymore...

 

[phxsportz]

Strange indeed...  ok.. try this

Dim popup As Boolean 
popup = FALSE

If Range("c4") = "Shortage" Then 
   popup = True 
ElseIf Range("c5") = "Shortage" Then 
   popup = True 
ElseIf Range("c6") = "Shortage" Then 
   popup = True 
End If 
If popup Then 
   MsgBox "Attention! Shortage on Brackets, Inform Supervisor Immediately!", vbOKOnly 
End If

 

[Norie]

Could you please explain exactly what you want to do?

Which sheet/range do you want to monitor?

By the way you do realize that the Target parameter passed to the code is the range that has been changed.

 

[peter85]

Well I would like to simplify it a bit. I would like it that if one assigned cell (let's say C3) shows the word ''Shortage", a pop-up occurs once, at the moment the pop-up occurs each time a cell in the worksheet is entered data... I also want this pop-up to occur when I'm working in a different worksheet and data in this sheet is linked to the "pop-up sheet" and triggers cell C3 to change into "Shortage"