Excel entry must be negative integer

Posted on 2012-03-11
Medium Priority
Last Modified: 2012-03-12
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,
Question by:mike637
  • 2
  • 2
LVL 42

Accepted Solution

dlmille earned 2000 total points
ID: 37708343
Why not data validation?

See example attached.


Expert Comment

ID: 37708344
what i did in the following is; i selected the cells you listed and named a range as "negativesonly" then on the Worksheet_Change event for that sheet i added the following code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Application.Range("negativesonly")
    Dim r As Range
    For Each r In rng
        If Target.Address = r.Address Then
            If Target.Value2 >= 0 Then
                MsgBox "Value at " & r.Address & " must be negative number" & vbCrLf & "Please enter a valid negative number", vbCritical
            End If
        End If
End Sub

Open in new window


Author Closing Comment

ID: 37708345
Works for me - I tried validation, but I see I made an error in my original attempt.

My brain is a bit fried.


Author Comment

ID: 37708386
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.
LVL 42

Expert Comment

ID: 37710531
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:

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.EnableEvents = True
        End If
    End If
End Sub

Open in new window

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.


Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question