Solved

Excel entry must be negative integer

Posted on 2012-03-11
5
387 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

724 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