[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel entry must be negative integer

Posted on 2012-03-11
5
Medium Priority
?
393 Views
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,
meck637
0
Comment
Question by:mike637
  • 2
  • 2
5 Comments
 
LVL 42

Accepted Solution

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

See example attached.

Dave
dataValidation-r1.xls
0
 
LVL 5

Expert Comment

by:Aruiz04
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
                Target.Select
            End If
        End If
    Next
End Sub

Open in new window

0
 

Author Closing Comment

by:mike637
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.

Thanks.
0
 

Author Comment

by:mike637
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.
0
 
LVL 42

Expert Comment

by:dlmille
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.Undo
            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.

Dave
checkIfValid-r1.xls
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

865 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