how to specify Pop up settings?

peter85

New Member
Joined
Sep 2, 2006
Messages
9
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..
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
how about...

Code:
dim popup to boolean

If Range("c4") = "Shortage"
   popup = True
elseIf Range("c5") = "Shortage"
   popup = True
elseIf Range("c6") = "Shortage"
   popup = True
Else
   popup = False
Endif
if popup
   MsgBox "Attention! Shortage on Brackets, Inform Supervisor Immediately!" 
endif
 
Upvote 0
Is this code located in the Activate worksheet module?

If it is perhaps something like this will work?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("C4:C6")) Is Nothing Then Exit Sub
    
    Select Case Target.Row
        Case 4
            If Target = "Shortage" Then
                MsgBox "Attention! Shortage on Brackets, Inform Supervisor Immediately!"
            End If
        Case 5
            If Target = "Shortage" Then
                MsgBox "Attention! Shortage on P/UP Cable, Inform Supervisor Immediately!"
            End If
        Case 6
            If Target = "Shortage" Then
                MsgBox "Attention! Shortage on Skew Screw, Inform Supervisor Immediately!"
            End If
        Case Else
            ' do nothing
    End Select

End Sub
 
Upvote 0
Thanks for the reply's, however, they're not working:( any other solutions?

When you say they're not working, can you be more specific ?

Are they still coming up everytime a change is made ? (and they will if any 3 of those cells contain the word "SHORTAGE").

Or is it not popping up at all ? Do you only want it to popup once ?

detail details details ;)
 
Upvote 0
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("C4:C6")) Is Nothing Then Exit Sub

Select Case Target.Row
Case 4
If Target = "Shortage" Then
MsgBox "Attention! Shortage on Brackets, Inform Supervisor Immediately!"
End If
Case 5
If Target = "Shortage" Then
MsgBox "Attention! Shortage on P/UP Cable, Inform Supervisor Immediately!"
End If
Case 6
If Target = "Shortage" Then
MsgBox "Attention! Shortage on Skew Screw, Inform Supervisor Immediately!"
End If
Case Else
' do nothing
End Select

End Sub


this one does nothing at all...
 
Upvote 0
Private Sub Worksheet_Change(ByVal Target As Range)

dim popup to 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!"
End If

End Sub


This one shows: compile error, syntax error
 
Upvote 0
For the IF ElseIF

and My bad on the msgbox..

Code:
   MsgBox "Attention! Shortage on Brackets, Inform Supervisor Immediately!", vbOKOnly

And should be
Code:
Dim popup AS boolean
(my typo's are out of control today)
 
Upvote 0

Forum statistics

Threads
1,223,195
Messages
6,170,660
Members
452,344
Latest member
LarryRSch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top