data validation

C

Choice

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
 
B

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)

Choice said:
i have a list of phone numbers (C1:C100), in A1 you enter in a phone
Click to expand...
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.
 
C

choice

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
 
B

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)

choice said:
This is what i put in but nothing seems to be working, im not sure if i am
Click to expand...
supposed to change anything with the worksheet function
 
B

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)
 
Top