mike637
asked on
Excel entry must be negative integer
Hello Experts,
I have 5 cells in my worksheet that must be keyed as a negative interger with 2 decimals points.
Range (G6, G9, G10, G11, G20) all must be keyed as negative or a message box appears stating that entry must be entered as negative number.
Can you assist me in setting my worksheet change to monitor these cells and msg box anything that is not in compliance.
Thank you,
meck637
I have 5 cells in my worksheet that must be keyed as a negative interger with 2 decimals points.
Range (G6, G9, G10, G11, G20) all must be keyed as negative or a message box appears stating that entry must be entered as negative number.
Can you assist me in setting my worksheet change to monitor these cells and msg box anything that is not in compliance.
Thank you,
meck637
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works for me - I tried validation, but I see I made an error in my original attempt.
My brain is a bit fried.
Thanks.
My brain is a bit fried.
Thanks.
ASKER
Is there any way we can reopen this one and award Aruiz04 - 400 points and dlmille 100 points. I tried the first solution and it did not totally fit my needs however - I achieved what I needed with Aruiz04's solution.
If you'd like, you can request attention to do that. You had accepted mine before I had a chance to submit this, however, which does an UNDO so the previous entry remains.
HOWEVER - PLEASE NOTE: Aruizo4's solution still allows the change to happen, though you get a warning message.
Here's your code:
See attached.
However, I still fail to see how data validation doesn't work properly for you. If you'd explain perhaps I can help with that as non-vba approaches can be less maintenance.
Dave
checkIfValid-r1.xls
HOWEVER - PLEASE NOTE: Aruizo4's solution still allows the change to happen, though you get a warning message.
Here's your code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCheckRanges As Range
Set rCheckRanges = Union(Range("G6"), Range("G9"), Range("G10"), Range("G11"), Range("G20"))
If Not Intersect(Target, rCheckRanges) Is Nothing Then
If Target.Value >= 0 Then
MsgBox "Negatives only, please try again!", vbCritical, "Aborting!"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End If
End Sub
See attached.
However, I still fail to see how data validation doesn't work properly for you. If you'd explain perhaps I can help with that as non-vba approaches can be less maintenance.
Dave
checkIfValid-r1.xls
Open in new window