[Solved] [] [] VBA: Worksheet_Change doesn&#039;t fire when

• Hi Guys!

I know this is Noddy stuff, but I can't see the wood for the trees!

Think of a worksheet.
Put a number in B4
Put another number in B7
Put a number between 0.1 and 25 in B8
Put this formula in B9:

=B7*EXP(GAMMALN(1+(1/B8)))

And this formula in C9:

=B4-B9

Now put this code in the worksheet's code module:

Private Sub Worksheet_Change(ByVal Target As Range)

Range("C9").GoalSeek Goal:=0, ChangingCell:=Range("B7")
Else

End Sub

Change the value in B8

Explain why the Goal Seek doesn't. Happen that is :wink1:

Any ideas!

Cheers,
Chris

&amp;quot;Varium et mutabile semper Excel&amp;quot;

• Hi Relman,

Once the Else code was replaced with End If it worked for me.

Range("C9").GoalSeek Goal:=0, ChangingCell:=Range("B7")
End If
End Sub

What does happen?
Try placing a BreakLine on the GoalSeek line of code and the change B8 to see what happens.
(Go to the line of code the press F9 to toogle breakline on and off)

Cheers
Andy

[h4]Cheers
Andy
[/h4]

• Thanks for the quick replies Guys!

Ritchie: I can't believe I'm so stupid! TVM

Andy: Nothing happens. When I change the value of B8, B9 recalculates, but B7 stays the same: I want it to change until C9=0 :-{

It's as tho' VBA isn't detecting the changed cell... possible? Likely? Fixable?

The Goal Seek bit works just great on a button . . . DTH?

Cheers,
Chris

&amp;quot;Varium et mutabile semper Excel&amp;quot;

• Hi Chris,

Try this revised code. It's not different but it should display the before and after values.

Code
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim vntTemp
vntTemp = Range("B7")
Range("C9").GoalSeek Goal:=0, ChangingCell:=Range("B7")
MsgBox "Was " & vntTemp & " now " & Range("B7"), vbInformation
End If
End Sub``````

Here is the results from my test.
B4 = 2
B7 = 3
B8 = 4

Revised B7 = 2.206525303

Cheers
Andy

[h4]Cheers
Andy
[/h4]

• Thanks for your help Andy and Ritchie

It was my bad all along I had the VBE Design Mode button selected - DOH!

Deselected it and now it works fine, of course.

Thanks again for your time and perseverence in the face of utter stupid-idlyness!

Cheers,
Chris

&amp;quot;Varium et mutabile semper Excel&amp;quot;

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!