Pop up message upon specific cell entry?

[Guest]

Is there a way to set up a column that any time a "0" is entered in any cell
in that column then a pop up message will display? Saying something like:
"Are you certain this entry is correct?"

I have very little experience with writing macros, but am otherwise
generally well-versed in Excel. Data validation is quite capable of this
function... I don't think.

Any help would be appreciated.

 

[Vergel Adriano]

Try pasting this in the code module of your worksheet. It traps the Change
event. It assumes that the column to watch is column A. If you enter a "0"
in any row of column A, you get a message box where you can say Yes or No.
If you say Yes, it allows you to proceed, if you say No, it clears the cell.

This will work when the change involves only one cell. If you paste "0" for
example to multiple rows in column A, it will not do anything.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Target.Column = 1 Then
If IsNumeric(Target.Text) And Target.Value = 0 Then
If vbNo = MsgBox("Are you certain this entry is correct?",
vbYesNo + vbQuestion) Then
Target.Value = ""
Target.Select
End If
End If
End If
End Sub

 

[Debra Dalgleish]

Yes, you could do this with data validation:

Select the cell (C3 in this example), and choose Data>Validation
For Allow, choose Custom
In the Formula box, enter: =C3<>0

 

[Guest]

You could try right clicking on your tab, select view code and paste the
following into the code module. Change the range from B:B to whatever range
you need to test for. The NG may wrap some of the lines, so look for any
lines that are colored red - it is probably one line that got split into two
lines.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngAnswer As Long

If IsEmpty(Target) Then Exit Sub

On Error GoTo CleanUp
Application.EnableEvents = False

If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then
If Target.Value = 0 Then
lngAnswer = MsgBox("Are you certain this entry is correct?", vbYesNo)
If lngAnswer = vbNo Then
Target.ClearContents
Target.Select
End If
End If
End If

CleanUp:
Application.EnableEvents = True
End Sub

 

[Guest]

Thanks a ton. This seems to work perfectly. Much appreciated.

 

[Guest]

Wow. I didn't think this would work... but it does! This is a very simple
(and effective) solution. Thanks, Debra. -Matt

 

[Debra Dalgleish]

You're welcome! Using data validation, users won't have to enable
macros, if there's no other code in the workbook.

 

[Guest]

You're welcome -although I think Debra's suggestion is better. I did not
realize the custom option would ask if you wanted to continue and allow users
the option to put a 0 in the cell if that is their intent.

 

[Mukesh]

Debra,

What if the cell is empty, what would be the formula for an alert?
Lets say my validation cell is A2, if A1 is empty and some data is
typed in cell A2, I need an alert, is it possible?

Thanks.
Mukesh