data validation

[Guest]

i have a list of phone numbers (C1:C100), in A1 you enter in a phone number, if the number you enter is from the list, nothing happens, if it is not on the list, i need a text box to pop up saying "Continue" then yes or no. if yes, i need Macro1, if no, nothing to happen

Thanks in advance

 

[Bob Phillips]

Dim ans
If WorksheetFunction.CountIf(Range("C1:C100"), Range("A1")) > 0 Then
ans = MsgBox("Continue", vbYesNo)
If ans = vbYes Then macro1
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

i have a list of phone numbers (C1:C100), in A1 you enter in a phone

number, if the number you enter is from the list, nothing happens, if it is
not on the list, i need a text box to pop up saying "Continue" then yes or
no. if yes, i need Macro1, if no, nothing to happen.

 

[Guest]

This is what i put in but nothing seems to be working, im not sure if i am supposed to change anything with the worksheet functio

Dim an
If WorksheetFunction.CountIf(Range("Tracking!C4:C1000"), Range("Sale!P2")) > 0 The
ans = MsgBox("Please Enter in Tracker", vbYesNo
If ans = vbYes Then SortTracke
End If

 

[Bob Phillips]

Put it in a worksheet change event

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans
If Target.Address = "$A$1" Then
If WorksheetFunction.CountIf(Range("C1:C100"), Range("A1")) > 0 Then
ans = MsgBox("Continue", vbYesNo)
If ans = vbYes Then macro1
End If
End If

End Sub

right-click on the sheet tab, select View Code, and paste it in

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

This is what i put in but nothing seems to be working, im not sure if i am

supposed to change anything with the worksheet function

 

[Bob Phillips]

This might be better in the tracking worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans
If Target.Address = "$A$1" Then
If WorksheetFunction.CountIf(Range("C4:C100"), Range("Sale!P2")) > 0
Then
ans = MsgBox("Continue", vbYesNo)
If ans = vbYes Then macro1
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)