[Solved] [] [] VBA: Worksheet_Change doesn'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)


    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!

    Cheers,
    Chris


    "Varium et mutabile semper Excel"

  • 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

  • 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


    "Varium et mutabile semper Excel"

  • 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

  • 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


    "Varium et mutabile semper Excel"

Participate now!

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