code doesnt work
It all depends upon what you want to trigger the check. If it is to be data
in the Tracking sheet, C4:C100, then it should be Private Sub Worksheet_Change(ByVal Target As Range) Dim ans If Target.Address = "C4:100" Then If WorksheetFunction.CountIf(Range("C4:C100"), Range("Sale!P2")) 0 Then ans = MsgBox("Continue", vbYesNo) If ans = vbYes Then Application.Run (SortTracker) End If End If End Sub If it is to be a change to P2 on tghe Sale sheet it should be Private Sub Worksheet_Change(ByVal Target As Range) Dim ans If Target.Address = "$P$2" Then If WorksheetFunction.CountIf(Range("Tracking!C4:C100" ), Range("P2")) 0 Then ans = MsgBox("Continue", vbYesNo) If ans = vbYes Then Application.Run (SortTracker) End If End If End Sub You cannot have a cell on one sheet triggering an event on another. And you must put it in the worksheet nmodule. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Choice" wrote in message ... Still not working, this is exactly what i have in there. when i go to Sale!P2...type in a number nothing happens Private Sub Worksheet_Change(ByVal Target As Range) Dim ans If Target.Address = "sale!$P$2" Then If WorksheetFunction.CountIf(Range("Tracking!C4:C100" ), Range("Sale!P2")) 0 Then ans = MsgBox("Continue", vbYesNo) If ans = vbYes Then Application.Run (SortTracker) End If End If End Sub |
code doesnt work
when i type a number into sale!p2 and hit enter, i want it to check to see if that number is in tracking!C4:C1000. if its not then i want the message to come up
|
code doesnt work
This should work
Private Sub Worksheet_Change(ByVal Target As Range) Dim ans If Target.Address = "$P$2" Then If WorksheetFunction.CountIf(Worksheets("Tracking"). _ Range("C4:C100"), Range("P2").Value) 0 Then ans = MsgBox("Continue", vbYesNo) If ans = vbYes Then SortTracker End If End If End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "choice" wrote in message ... when i type a number into sale!p2 and hit enter, i want it to check to see if that number is in tracking!C4:C1000. if its not then i want the message to come up |
All times are GMT +1. The time now is 12:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com