Worksheet_Change
 

I would like to know if its possible to first CHECK if Cell.value (its
a value of a Data Validation Dropdown) is being deleted using DELETE
button on keyboard, & then capture this initial value of the Cell &
write it to a Helper worksheet along with the Cell.address &
Cell.Offset(0,1).value.

This Cell.value, will be used later to do a .Find in a column of Names
on another worksheet & then deduct this amount from the value in the
offset of the Found Cell (e.g. Sam).

Please note i am using the worksheet_Change event.

If there is an alternative Function or Sub to check for cell deletion
(using Delete button), then please let me know.

Sheet1
---------
A B
Row2 Sam 20


Sheet2
---------
A B C
Sam 20 $A$2


Sheet3
---------
A B
Sam 150 (later, should be 150-20=130)


Looking forward to your valued suggestions.

Worksheet_Change
 

No, there isn't an alternative

Private Sub Worksheet_Change(ByVal Target As Range)
If target.Address = "$B$9" then
if isempty(Target.Value) then
Application.EnableEvents = False
application.Undo
v = Target.Value
Target.ClearContents
Range("IV10").Value = v
Application.EnableEvents = True
end if
End Sub

--
Regards,
Tom Ogilvy

End Sub



"noname" wrote:

I would like to know if its possible to first CHECK if Cell.value (its
a value of a Data Validation Dropdown) is being deleted using DELETE
button on keyboard, & then capture this initial value of the Cell &
write it to a Helper worksheet along with the Cell.address &
Cell.Offset(0,1).value.

This Cell.value, will be used later to do a .Find in a column of Names
on another worksheet & then deduct this amount from the value in the
offset of the Found Cell (e.g. Sam).

Please note i am using the worksheet_Change event.

If there is an alternative Function or Sub to check for cell deletion
(using Delete button), then please let me know.

Sheet1
---------
A B
Row2 Sam 20


Sheet2
---------
A B C
Sam 20 $A$2


Sheet3
---------
A B
Sam 150 (later, should be 150-20=130)


Looking forward to your valued suggestions.

Worksheet_Change
 

Hi Tom,

Actually, I have a workbook in which there are say 3 sheets...
Sheet1 - contains a vertical column of Users Names in Col A, Col B
contains their (some values) values.

Sheet2 - has a column B which is filled with Data Validation dropdowns
(using a dynamic range from Helper sheet)

Sheet3 - Helper sheet, its use is to store the current value of the
active data validation cell, along with the cells address &
cell.offset(0,1).value. These values are stored in 3 consecutive cells
say A2, B2, C2 in the Helper Sheet. The Helper sheet also contains
some
User names Range=D2:D7.

Now, I am trying to achieve 3 things:

1] if in Sheet2, Data Validation cell is initially blank & i select a
User's name from it & want to add the cell.offset(0,1).value (its a
number) to Sheet1, it will scan the Col A in Sheet1 for the User's
name
& then put the value in the Found User's name cell.offset(0,1).

2] if in sheet2, i select another user in the same dropdown list,
then,
previously added value should be removed from previous user in sheet1
&
added to this new selected User in sheet1.

3] If in sheet2, i use Delete button to just delete the User's name
value in the dropdown data validation cell, then it should remove that
amount from the user in sheet1.

Please note that it should remove the amount values from previous user
&
add it properly to new user or incase of using delete button, it
should
delete amount from correct user.

How to accomplish this? Please help!


Sheet1
---------
A B
Sam 150 (later, should be 150-20=130)
Mike 230
John 160


Sheet2
---------
A B
Row2 Sam 20



Sheet3 (Helper Sheet)
---------
A B C D
Sam 20 $A$2 Sam
John
Mike