Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

limitMyEntry

Posted on 2013-01-29
3
Medium Priority
?
200 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 800 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 1200 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
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.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

650 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