Help please to convert a script from Worksheet_change to Worksheet_Calculate

[Recon]

Hi, I thought maybe this problem should have it's own thread as its changed from my initial challenge!

I need help please to convert the following script from Worksheet_change to Worksheet_calculate as the cell that triggers the event is actually altered as a result of a formula, not manually by the user.



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = Range("B5")
If Not Intersect(Target, rng) Is Nothing Then
If rng = "FO" Then
If Range("R1").Value >= "1" Then Exit Sub
MsgBox ("Congratulations, you have peen promoted to " & Range("b5").Value)
Range("R1") = Range("R1") + 1
ElseIf rng = "FL" Then
If Range("R2").Value >= "1" Then Exit Sub
MsgBox ("Congratulations, you have peen promoted to " & Range("b5").Value)
Range("R2") = Range("R2") + 1
End If
End If
Set rng = Nothing
End Sub


Can someone advise please how to convert this over? Other than this issue the script seems to work pretty well.

Thanks very much!

Recon.

 

[Andrew Poulsom]

What's the formula in B5? Are its precedent cells entered by the user?

 

[Recon]

Thanks for your reply,

yeah B5 is a series of words depending on a number of if statements, (ie if the user enters 5 in column X and 6 in column Y, the if statement generates the rank of "flight officer" for example, this rank then appears in B5) esentially the outputs will be a series of ranks such as "flight officer" or "pilot officer" with the popups generating a message saying "congrats you have reached the level of (whichever rank it is)".

It works great in testing when i manually enter the rank into b5, but when it autogenerates into the cell no cigar.

I think (the actual sheet is at home, just trying to configure the macro here at work) the actual value in b5 may just be a transposition of another cell in another worksheet where the formula is actually generated, but this shouldnt make a difference will it?

 

[p45cal]

Maybe, just maybe:

Private Sub Worksheet_Calculate()
Dim rng As Range
If Range("B5") = "FO" Then Set rng = Range("R1") Else If Range("B5") = "FL" Then Set rng = Range("R2")
If Not rng Is Nothing Then
  If rng.Value < 1 Then
    MsgBox ("Congratulations, you have peen promoted to " & Range("b5").Value)
    rng.Value = rng.Value + 1
  End If
End If
End Sub

 

[Recon]

hey p45Cal, that seems to have worked! thanks mate, really appreciate you taking the time, and wow, your way is so much more elegant that the way i was trying to do it! I still have so much to learn!