Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
When content of a cell changes, content of another deletes
If a formulated value in A1 changes to any value after a value is
manually entered in B1, I need the content of B1 to be deleted. In other words, 1) A1 first reflects a formulated value, 2) a value is manually entered into B1, 3) input elsewhere causes A1 to return a different calculated value, 4) thus causing B1 to delete its input. Is there a simple macro that will accomplish this? Thanks in advance. Michael |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
When content of a cell changes, content of another deletes
Paste the following code into the sheet module in the VBA Editor for the
worksheet where you want this to happen (Alt-F11 to open the VBA Editor, then Ctrl-R to display the Project Explorer. For Sheet1, click on Sheet1, etc.) Private LastVal Private Sub Worksheet_Activate() LastVal = Range("A1").Value End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$1" Then Exit Sub If Range("A1").Value < LastVal Then Range("B1").Delete LastVal = Range("A1").Value End If End Sub Hope this helps, Hutch " wrote: If a formulated value in A1 changes to any value after a value is manually entered in B1, I need the content of B1 to be deleted. In other words, 1) A1 first reflects a formulated value, 2) a value is manually entered into B1, 3) input elsewhere causes A1 to return a different calculated value, 4) thus causing B1 to delete its input. Is there a simple macro that will accomplish this? Thanks in advance. Michael |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
When content of a cell changes, content of another deletes
Hutch,
Your macro works great but with one problem. There are other cells in the same column as B1. When B1's contents delete, the cells beneath B1 change position. For example, B10 repositions to become B9, and then B8 with the next change to the contents of A1. They continue to migrate one row at a time. Do you have a further suggestion? Thanks. Michael |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
When content of a cell changes, content of another deletes
Replace Range("B1").Delete with
Range("B1").ClearContents Hope this helps, Hutch " wrote: Hutch, Your macro works great but with one problem. There are other cells in the same column as B1. When B1's contents delete, the cells beneath B1 change position. For example, B10 repositions to become B9, and then B8 with the next change to the contents of A1. They continue to migrate one row at a time. Do you have a further suggestion? Thanks. Michael |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
When content of a cell changes, content of another deletes
Thanks Hutch. It appears to be working just as anticipated.
Michael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Content in one coulumn using given content in another | Excel Worksheet Functions | |||
Deleting Rows - only deletes content | Excel Discussion (Misc queries) | |||
cell automatically deletes content if cell is selected | Excel Discussion (Misc queries) | |||
copy comment content to cell content as data not as comment | Excel Discussion (Misc queries) | |||
How to transpose formulas from column content to row content. | Excel Worksheet Functions |