Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_Change | Excel Programming | |||
Worksheet_Change | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |