ExcelBanter - preventing data entry if one cell's value smaller then zero

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - preventing data entry if one cell's value smaller then zero (https://www.excelbanter.com/excel-programming/411845-preventing-data-entry-if-one-cells-value-smaller-then-zero.html)

taco

preventing data entry if one cell's value smaller then zero

Hi everyone;

On my worksheet, users can enter data on E6:J185 range. and C3 cell is
counting down from 4.500 the total entered amount. What I would like to do
is, while users are entering data, if C3's value is getting smaller than
zero, code should warn them and prevent further data entry. Here is my
unsuccesful code;
Dim OldValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
If (c3) < 0 Then
On Error GoTo Whoops
Application.EnableEvents = False
MsgBox "You Dont Have Enough Points!"
Target.Value = OldValue
End If
Whoops:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If (c3) < 0 Then OldValue = Target.Value
End Sub

Thanks a lot in advance for your time and help.

Regards.

OssieMac

preventing data entry if one cell's value smaller then zero

Try creating another worksheet to save the old values at a corresponding cell
address if the entry is valid and if the target is not valid then copy the
old value back to the Target. Something like the following example:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strAddress As String

'Save the address of Target
strAddress = Target.Address

If Range("C3") = 0 Then
'Target entry OK so
'Save the entered value in Sheets("OldValue") at
'address matching main worksheet
Sheets("OldValues").Range(strAddress) = Target.Value
Else
MsgBox "You Dont Have Enough Points!"
'Copy the previously saved value for the address to target
Target.Value = Sheets("OldValues").Range(strAddress)
End If

End Sub

--
Regards,

OssieMac

"taco" wrote:

Hi everyone;

On my worksheet, users can enter data on E6:J185 range. and C3 cell is
counting down from 4.500 the total entered amount. What I would like to do
is, while users are entering data, if C3's value is getting smaller than
zero, code should warn them and prevent further data entry. Here is my
unsuccesful code;
Dim OldValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
If (c3) < 0 Then
On Error GoTo Whoops
Application.EnableEvents = False
MsgBox "You Dont Have Enough Points!"
Target.Value = OldValue
End If
Whoops:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If (c3) < 0 Then OldValue = Target.Value
End Sub

Thanks a lot in advance for your time and help.

Regards.

Norman Jones[_2_]

preventing data entry if one cell's value smaller then zero

Hi Taco,

Try:

'==========
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim Rng2 As Range

With Me
Set Rng = .Range("E6:J185")
Set Rng2 = .Range("C3")
End With

If Not Intersect(Rng, Target) Is Nothing Then
If Rng2.Value <= 0 Then
On Error GoTo 0
Application.EnableEvents = False
MsgBox "You Dont Have Enough Points!"
Application.Undo
End If
End If
Whoops:
Application.EnableEvents = True
End Sub
'<<==========

---
Regards.
Norman

"taco" wrote in message
...
Hi everyone;

On my worksheet, users can enter data on E6:J185 range. and C3 cell is
counting down from 4.500 the total entered amount. What I would like to do
is, while users are entering data, if C3's value is getting smaller than
zero, code should warn them and prevent further data entry. Here is my
unsuccesful code;
Dim OldValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
If (c3) < 0 Then
On Error GoTo Whoops
Application.EnableEvents = False
MsgBox "You Dont Have Enough Points!"
Target.Value = OldValue
End If
Whoops:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If (c3) < 0 Then OldValue = Target.Value
End Sub

Thanks a lot in advance for your time and help.

Regards.

Norman Jones[_2_]

preventing data entry if one cell's value smaller then zero

Hi Taco,

On Error GoTo 0

Should read:

On Error GoTo Whoops

---
Regards.
Norman

"Norman Jones" wrote in message
...
Hi Taco,

Try:

'==========
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim Rng2 As Range

With Me
Set Rng = .Range("E6:J185")
Set Rng2 = .Range("C3")
End With

If Not Intersect(Rng, Target) Is Nothing Then
If Rng2.Value <= 0 Then
On Error GoTo 0
Application.EnableEvents = False
MsgBox "You Dont Have Enough Points!"
Application.Undo
End If
End If
Whoops:
Application.EnableEvents = True
End Sub
'<<==========

---
Regards.
Norman

taco

preventing data entry if one cell's value smaller then zero

Hi Norman;

Thank you very much. I really appreciated. It's works perfectly.

"Norman Jones" wrote:

Hi Taco,

Try:

'==========
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim Rng2 As Range

With Me
Set Rng = .Range("E6:J185")
Set Rng2 = .Range("C3")
End With

If Not Intersect(Rng, Target) Is Nothing Then
If Rng2.Value <= 0 Then
On Error GoTo 0
Application.EnableEvents = False
MsgBox "You Dont Have Enough Points!"
Application.Undo
End If
End If
Whoops:
Application.EnableEvents = True
End Sub
'<<==========

---
Regards.
Norman

"taco" wrote in message
...
Hi everyone;

On my worksheet, users can enter data on E6:J185 range. and C3 cell is
counting down from 4.500 the total entered amount. What I would like to do
is, while users are entering data, if C3's value is getting smaller than
zero, code should warn them and prevent further data entry. Here is my
unsuccesful code;
Dim OldValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
If (c3) < 0 Then
On Error GoTo Whoops
Application.EnableEvents = False
MsgBox "You Dont Have Enough Points!"
Target.Value = OldValue
End If
Whoops:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If (c3) < 0 Then OldValue = Target.Value
End Sub

Thanks a lot in advance for your time and help.

Regards.

taco

preventing data entry if one cell's value smaller then zero

Thanks a lot OssieMac. Appreciated.

"OssieMac" wrote:

Try creating another worksheet to save the old values at a corresponding cell
address if the entry is valid and if the target is not valid then copy the
old value back to the Target. Something like the following example:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strAddress As String

'Save the address of Target
strAddress = Target.Address

If Range("C3") = 0 Then
'Target entry OK so
'Save the entered value in Sheets("OldValue") at
'address matching main worksheet
Sheets("OldValues").Range(strAddress) = Target.Value
Else
MsgBox "You Dont Have Enough Points!"
'Copy the previously saved value for the address to target
Target.Value = Sheets("OldValues").Range(strAddress)
End If

End Sub

--
Regards,

OssieMac

"taco" wrote:

Hi everyone;

On my worksheet, users can enter data on E6:J185 range. and C3 cell is
counting down from 4.500 the total entered amount. What I would like to do
is, while users are entering data, if C3's value is getting smaller than
zero, code should warn them and prevent further data entry. Here is my
unsuccesful code;
Dim OldValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
If (c3) < 0 Then
On Error GoTo Whoops
Application.EnableEvents = False
MsgBox "You Dont Have Enough Points!"
Target.Value = OldValue
End If
Whoops:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If (c3) < 0 Then OldValue = Target.Value
End Sub

Thanks a lot in advance for your time and help.

Regards.

All times are GMT +1. The time now is 06:44 PM.