Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

limitMyEntry

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
Rayne
Asked:
Rayne
2 Solutions
 
SteveCommented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
RayneAuthor Commented:
Thanks all :)
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now