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.
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.