Solved

limitMyEntry

Posted on 2013-01-29
3
192 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
[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
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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…

696 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