Solved

limitMyEntry

Posted on 2013-01-29
3
173 Views
Last Modified: 2013-01-30
Hello All,

I am looking for a worksheet change event:

Requirements:
1.      For columns C and F, users cannot enter negative numbers
2.      If users enter negative, target.value becomes 0 and prompts users with message
3.      If user either enter numbers for C or F, the column H of that row will get TRUE for the update.

thanks
limitInput.xlsx
0
Comment
Question by:Rayne
3 Comments
 
LVL 24

Assisted Solution

by:Steve
Steve earned 200 total points
ID: 38834194
You could apply Validation rules to only allow positive numbers in a range.
This will have it's own message box.

This is in Data > Validatation > Allow > Decimal > Above > 0

See attached.
Validation.xlsx
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 300 total points
ID: 38834265
Right-click on the sheet tab name
Select View code
Paste this code in the VBA window
Close the VBA window
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 And Not Intersect(Target, Range("C4:C9,F4:F9")) Is Nothing Then
        If Target.Value < 0 Then
            Target.Value = 0
            MsgBox "Only non-negative numbers allowed", , "Warning"
        End If
        Target.EntireRow.Cells(8).Value = True
    End If
End Sub

Open in new window


*****Code edited*****
0
 

Author Closing Comment

by:Rayne
ID: 38835910
Thanks all :)
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

821 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