Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Preventing double entry formula | Excel Worksheet Functions | |||
Data Entry Online, Data Format, Data Conversion and Data EntryServices through Data Entry Outsourcing | Excel Discussion (Misc queries) | |||
"Hot spot" in a spreadsheet that will duplicate the cell's entry to another cell, or to activate a macro | Excel Programming | |||
Preventing manual entry in dropdown list ... possible? | Excel Discussion (Misc queries) | |||
Saving AutoFiltered Data off as a smaller file? | Excel Programming |