[Solved] [] [] VBA: Worksheet_Change doesn't fire when

Relman · Nov 13th 2003

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)

If Target.Address = "$B$8" Then
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!

Richie(UK) · Nov 13th 2003

Hi R,

It worked when I tried it!

(I just changed the 'Else' to an 'End If') ;;)

Is there more to it than this?

HTH

**Andy Pope** · Nov 13th 2003

Hi Relman,

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

If Target.Address = "$B$8" Then
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

Relman · Nov 13th 2003

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?

**Andy Pope** · Nov 13th 2003

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
    If Target.Address = "$B$8" Then
        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

Relman · Nov 13th 2003

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!

[Solved] [] [] VBA: Worksheet_Change doesn't fire when

Participate now!

Share