PDA

View Full Version : VBA Event Change Using Dependents



DMain
02-02-2015, 03:55 AM
Hello,

To give a bit of background, I have an excel workbook where multiple calculations are performed on different sheets. In order to simplify the calculations I have created a summary sheet where I intend to change the input values and observe the output values which are linked to the relevant sheets. There are over 200 output values so I would like the vba code to give me a message box stating which values have changed.

In order to do this I plan to check for an change event using dependents. For instance if I change an input, using dependents I should be able to find which outputs have changed.

My code can be seen below.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dependents As Range
Set Dependents = Range(Target.Dependents.Count)
If InStr(Dependents, Range("F6:F233")) Is Nothing Then
MsgBox "Change"
End If
End Sub

I have two questions. Firstly, is this the right way to go about solving this problem? Secondly, is it possible to use dependents over multiple sheets?
I am fairly new to vba so any help would be appreciated.

xld
02-02-2015, 04:29 AM
It is possible to track dependencies over multiple sheets, but it is far from easy. I did something on a dependency tree a few years ago, I wouldn't advise anyone to try it.