0

I have a sheet that has plenty of data validation lists that are directly dependent on each other. So for example:

When I select a value from drop list in cell A2, it changes the list in cell A3 and thus changes the selected value on A3. This example is repeated over and over for different lists.

I am searching for a way to detect all the changes in those cells and write them (or their name in another column but same row) in another cell, just to tell the user what actually changed in the sheet. (we can also just change the color of the cells to reflect the change if that is easier)

The Worksheet_Change(ByVal Target As Range) doesn't seem to detect except the first change that happened in A2.

The question is quite generic but I can give an example if need. Any help is appreciated.

Zoe
  • 27,060
  • 21
  • 118
  • 148
afares
  • 11
  • 2
  • 2
    Can you provide the code that you have in **Worksheet_Change** event – Zac Jul 02 '20 at 11:42
  • `Worksheet_Change` is not triggered when a _formula_ changes a cells value. But, since the chain of changes is triggered by a user change in one cell, you could use the `Precedents` property of the cell that does trigger the event to reference cells that depend on it. – chris neilsen Jul 02 '20 at 11:48
  • @Zac I dont have a code yet. I am just building. I started with the following to see if I can detect the changes, then the code should be easy to read the changed cells and write them somewhere else: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B:B")) Is Nothing Then For Each cell In Target Debug.Print cell.Address Next cell End If End Sub – afares Jul 02 '20 at 11:52
  • Maybe you could do it with `Worksheet_Calculate` but not tested – Foxfire And Burns And Burns Jul 02 '20 at 11:55
  • @chrisneilsen thanks, I tried the precedents, it showed an error that there is no formula there. I tried also the Dependents (graphically) and it showed the right dependencies of that cell on data lists that I have in another sheet (called data). – afares Jul 02 '20 at 11:58
  • @FoxfireAndBurnsAndBurns `Worksheet_Calculate` doesn't have the target as argument and I didn't find a way to return what actually changed.. – afares Jul 02 '20 at 12:00
  • @afares so, if you found a property that gives you the References you need, is that not the answer to your question? – chris neilsen Jul 02 '20 at 12:04
  • You could use `ActiveCell` to detect the active cell in the momento `Calculate` is triggered. Or maybe [Application.Caller](https://stackoverflow.com/questions/11267597/more-information-about-application-caller-with-vba-in-excel) – Foxfire And Burns And Burns Jul 02 '20 at 12:12
  • @chrisneilsen not really but this could help. let me try to give an example. in A2 i have a list called list2 which is defined in sheet **data** and the selected value in A2 is called val2. The list in A3 is called list3 and the selected value is called val3. In **data** sheet I define the values of list3 as "i+val2 +1". Using `Dependents` (graphically), i can see that A2 has dependency with list3 (in data sheet). But I cant detect A3. Maybe I have to do another thing to detect it. Another question, i tried to loop through Target.Dependents and print their address but the vba didnt work. – afares Jul 02 '20 at 12:17
  • @FoxfireAndBurnsAndBurns, can I detect all the cells that changed using your method. What is active cell ? I have say 5 cells that change at the same time when the selected cell changed. My problem is in detecting the other cells not the selected one.. – afares Jul 02 '20 at 12:21

0 Answers0