Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to trap data entry outside of range
Hi,
I found this code from Bob Phillips that evaluates a cell entry against the previous cells entry in the column and generates a warning. Can anybody help me modify this code to evaluate an average of the previous 10 rows (i.e. a range)? Thanks. Alan Private Sub Worksheet_Change(ByVal Target As Range) Dim ans Application.EnableEvents = False On Error GoTo sub_exit With Target If (.Column 2 And .Column < 9) Then If .Row = 3 Or .Row = 8 Or .Row = 13 Or .Row = 14 Then If .Value .Offset(0, -1) * 1.2 Then ' 20% greater ans = MsgBox("Value of " & .Value & " is more than 20% greater " & _ "than the " & .Offset(0, -1).Value & " for " & Format(.Offset(-.Row + 1, -1), "dd mmm yyyy") & vbCrLf & _ vbCrLf & _ "Are you sure? Please recheck entry!" & vbCrLf & _ "(hit 'OK' to re-input, 'Cancel' to accept and ignore limit)", _ vbOKCancel, _ "Vinay's Value Checker") If ans = 2 Then .Offset(1, 0).Activate End If End If End If End If End With sub_exit: Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to trap data entry outside of range
use something like this
If .Value WorksheetFunction.Average(Range(.Offset(0, -1), ..Offset(0, -10))) Then You will have to assure that Offset(0, -1) and .Offset(0, -10) are not off the sheet, possibly by ignoring Targets too close to the top of the sheet using, perhaps using something like, If Intersect(Target, Range(.Rows(1),.Rows(10))) Then Exit Sub "Alan Campbell" wrote in message om... Hi, I found this code from Bob Phillips that evaluates a cell entry against the previous cells entry in the column and generates a warning. Can anybody help me modify this code to evaluate an average of the previous 10 rows (i.e. a range)? Thanks. Alan Private Sub Worksheet_Change(ByVal Target As Range) Dim ans Application.EnableEvents = False On Error GoTo sub_exit With Target If (.Column 2 And .Column < 9) Then If .Row = 3 Or .Row = 8 Or .Row = 13 Or .Row = 14 Then If .Value .Offset(0, -1) * 1.2 Then ' 20% greater ans = MsgBox("Value of " & .Value & " is more than 20% greater " & _ "than the " & .Offset(0, -1).Value & " for " & Format(.Offset(-.Row + 1, -1), "dd mmm yyyy") & vbCrLf & _ vbCrLf & _ "Are you sure? Please recheck entry!" & vbCrLf & _ "(hit 'OK' to re-input, 'Cancel' to accept and ignore limit)", _ vbOKCancel, _ "Vinay's Value Checker") If ans = 2 Then .Offset(1, 0).Activate End If End If End If End If End With sub_exit: Application.EnableEvents = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to trap data entry outside of range
Rows, not Columns: Range(.Offset(-1, 0), .Offset(-10, 0))
"Bob Kilmer" wrote in message ... use something like this If .Value WorksheetFunction.Average(Range(.Offset(0, -1), .Offset(0, -10))) Then You will have to assure that Offset(0, -1) and .Offset(0, -10) are not off the sheet, possibly by ignoring Targets too close to the top of the sheet using, perhaps using something like, If Intersect(Target, Range(.Rows(1),.Rows(10))) Then Exit Sub "Alan Campbell" wrote in message om... Hi, I found this code from Bob Phillips that evaluates a cell entry against the previous cells entry in the column and generates a warning. Can anybody help me modify this code to evaluate an average of the previous 10 rows (i.e. a range)? Thanks. Alan Private Sub Worksheet_Change(ByVal Target As Range) Dim ans Application.EnableEvents = False On Error GoTo sub_exit With Target If (.Column 2 And .Column < 9) Then If .Row = 3 Or .Row = 8 Or .Row = 13 Or .Row = 14 Then If .Value .Offset(0, -1) * 1.2 Then ' 20% greater ans = MsgBox("Value of " & .Value & " is more than 20% greater " & _ "than the " & .Offset(0, -1).Value & " for " & Format(.Offset(-.Row + 1, -1), "dd mmm yyyy") & vbCrLf & _ vbCrLf & _ "Are you sure? Please recheck entry!" & vbCrLf & _ "(hit 'OK' to re-input, 'Cancel' to accept and ignore limit)", _ vbOKCancel, _ "Vinay's Value Checker") If ans = 2 Then .Offset(1, 0).Activate End If End If End If End If End With sub_exit: Application.EnableEvents = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to trap data entry outside of range
Bob,
Thanks. I will give try your coding changes and let you know how is goes. Alan "Bob Kilmer" wrote in message ... Rows, not Columns: Range(.Offset(-1, 0), .Offset(-10, 0)) "Bob Kilmer" wrote in message ... use something like this If .Value WorksheetFunction.Average(Range(.Offset(0, -1), .Offset(0, -10))) Then You will have to assure that Offset(0, -1) and .Offset(0, -10) are not off the sheet, possibly by ignoring Targets too close to the top of the sheet using, perhaps using something like, If Intersect(Target, Range(.Rows(1),.Rows(10))) Then Exit Sub "Alan Campbell" wrote in message om... Hi, I found this code from Bob Phillips that evaluates a cell entry against the previous cells entry in the column and generates a warning. Can anybody help me modify this code to evaluate an average of the previous 10 rows (i.e. a range)? Thanks. Alan Private Sub Worksheet_Change(ByVal Target As Range) Dim ans Application.EnableEvents = False On Error GoTo sub_exit With Target If (.Column 2 And .Column < 9) Then If .Row = 3 Or .Row = 8 Or .Row = 13 Or .Row = 14 Then If .Value .Offset(0, -1) * 1.2 Then ' 20% greater ans = MsgBox("Value of " & .Value & " is more than 20% greater " & _ "than the " & .Offset(0, -1).Value & " for " & Format(.Offset(-.Row + 1, -1), "dd mmm yyyy") & vbCrLf & _ vbCrLf & _ "Are you sure? Please recheck entry!" & vbCrLf & _ "(hit 'OK' to re-input, 'Cancel' to accept and ignore limit)", _ vbOKCancel, _ "Vinay's Value Checker") If ans = 2 Then .Offset(1, 0).Activate End If End If End If End If End With sub_exit: Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code/Function to Look at Previous Entry to Validate Data | Excel Worksheet Functions | |||
How can you limit the data entry range in Excel? | Excel Worksheet Functions | |||
Highlight a range for data entry | Excel Discussion (Misc queries) | |||
How do I hide spreadsheet range from data entry? | Excel Worksheet Functions | |||
VBA code: how to invoke data entry mode | Excel Programming |