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.

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



"matt ball" wrote in message
...
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.




----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

Yes, you could do this with data validation:

Select the cell (C3 in this example), and choose DataValidation
For Allow, choose Custom
In the Formula box, enter: =C3<0
On the Error Alert tab, for Style, choose Warning
Enter your error message, and click OK

matt ball wrote:
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.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

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




"matt ball" wrote:

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.

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


"JMB" wrote:

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




"matt ball" wrote:

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.

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

"Debra Dalgleish" wrote:

Yes, you could do this with data validation:

Select the cell (C3 in this example), and choose DataValidation
For Allow, choose Custom
In the Formula box, enter: =C3<0
On the Error Alert tab, for Style, choose Warning
Enter your error message, and click OK

matt ball wrote:
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.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

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

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

"Debra Dalgleish" wrote:


Yes, you could do this with data validation:

Select the cell (C3 in this example), and choose DataValidation
For Allow, choose Custom
In the Formula box, enter: =C3<0
On the Error Alert tab, for Style, choose Warning
Enter your error message, and click OK

matt ball wrote:

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.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

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.

"matt ball" wrote:

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


"JMB" wrote:

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




"matt ball" wrote:

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.

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




"Debra Dalgleish" wrote:

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

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

"Debra Dalgleish" wrote:


Yes, you could do this with data validation:

Select the cell (C3 in this example), and choose DataValidation
For Allow, choose Custom
In the Formula box, enter: =C3<0
On the Error Alert tab, for Style, choose Warning
Enter your error message, and click OK

matt ball wrote:

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.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html