CODEWITHSUNDEEP
vbaexcel
DFH
DFH

Reputation: 45

Run Worksheet_Change if multiple defined cells are changes

I have the following code that runs a goalseek if the defined named range "N" changes. However, I want the code to run if any of several cells changes. E.g. "N1", "N2", etc..

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.EnableEvents = False


If Target.Address = Range("N").Address Then

'Goalseek for force equilibrium
Range("Delta_F").GoalSeek Goal:=0, ChangingCell:=Range("h_neutral")

End If


Application.EnableEvents = True

End Sub

I tried the following, but it did not work:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.EnableEvents = False


If Target.Address = Range("N, N1, N2").Address Then

'Goalseek for force equilibrium
Range("Delta_F").GoalSeek Goal:=0, ChangingCell:=Range("h_neutral")

End If


Application.EnableEvents = True

End Sub

Any help is appreciated. Thanks in advance.

Upvotes: 0

Views: 370

Answers (2)

urdearboy
urdearboy

Reputation: 14580

Testing your criteria as a Range is easier than testing the Address as a string.

Building on @Michal's solution, the below will only execute when your changed cell (Target) overlaps (Intersects) with your 3 ranges (Set as the variable TargetRange here). The main difference is the double negative in the test statement which allows you to avoid Exit Sub resulting in moderately cleaner code. 

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TargetRange As Range
Set TargetRange = Union(Range("B3"), Range("G19"), Range("N1"))

If Not Intersect(TargetRange, Target) Is Nothing Then
    Application.EnableEvents = False
        Range("Delta_F").GoalSeek Goal:=0, ChangingCell:=Range("h_neutral")
    Application.EnableEvents = True
End If

End Sub

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37367

Your condition can be easily checked with Intersect function:

If Intersect(Range("N"), Target) Is Nothing Then Exit Sub

If Target doesn't lie within your range, it will exit the sub (this condition should be placed as the first command in your Sub).

Upvotes: 0

Related Questions