VBA Pop Up Message Using Moving Cell Values

[h3artablaze]

Hi,
Just to preface, I'm very new to VBA so not sure if starting in the right place here. I'm trying to get a VBA error message to pop up depending on value in cell F5. If F5 = Q5 I'd like "Take Profit Level has been reached" to appear, whereas if F5=R5 I'd like "Stop/Loss Level has been reached" to appear. F5 is a changing value dependent on a function.

Private Sub Calculate(ByVal Target As Range)
If Target.Address = "$F$5" Then
If Target.Value = "$Q$5" Then MsgBox "Take Profit Level has been reached"
If Target.Value = "$R$5" Then MsgBox "Stop/Loss Level has been reached"
End If
End Sub

I went with the above after some light research but clearly is wrong...

Many thanks in advance

 

[dreid1011]

Hi,
Just to preface, I'm very new to VBA so not sure if starting in the right place here. I'm trying to get a VBA error message to pop up depending on value in cell F5. If F5 = Q5 I'd like "Take Profit Level has been reached" to appear, whereas if F5=R5 I'd like "Stop/Loss Level has been reached" to appear. F5 is a changing value dependent on a function.

Private Sub Calculate(ByVal Target As Range)
If Target.Address = "$F$5" Then
If Target.Value = "$Q$5" Then MsgBox "Take Profit Level has been reached"
If Target.Value = "$R$5" Then MsgBox "Stop/Loss Level has been reached"
End If
End Sub

I went with the above after some light research but clearly is wrong...

Many thanks in advance

Give this a try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$5" Then
    If Target.Value = Range("Q5").Value Then MsgBox "Take Profit Level has been reached"
    If Target.Value = Range("R5").Value Then MsgBox "Stop/Loss Level has been reached"
End If
End Sub

 

[Joe4]

Welcome to the Board!

F5 is a changing value dependent on a function.

dreid1011,
Because of that comment above, I do not think your code will work. Worksheet_Change only runs when hard-coded values are updated, not changes that happen due to formulas in that cell.

h3artablaze,
This is not valid:

Private Sub Calculate(ByVal Target As Range)
If Target.Address = "$F$5" Then

The proper call to the "Calculate" event it:

Private Sub Worksheet_Calculate()

Note how the name is "Worksheet_Calculate", and there is no parameter.
Event procedures are VERY strict. You cannot change the name or the parameters that they allow.

The issue with the Calculate event procedures is that they cannot identify which cell's value was updated/re-calculated. All that it can tell you is that some value somewhere on the sheet was re-calculated. So I don't think it is going to be helpful to you here.

What exactly is the formula in cell F5?
If it is a function of other cells whose values are hard-coded, then we can use a "Worksheet_Change" event procedure to watch for those values to change, and then do your check.

 

[dreid1011]

Welcome to the Board!


dreid1011,
Because of that comment above, I do not think your code will work. Worksheet_Change only runs when hard-coded values are updated, not changes that happen due to formulas in that cell.

Ah, fair enough. Thank you.

 

[h3artablaze]

Welcome to the Board!


dreid1011,
Because of that comment above, I do not think your code will work. Worksheet_Change only runs when hard-coded values are updated, not changes that happen due to formulas in that cell.

h3artablaze,
This is not valid:

Private Sub Calculate(ByVal Target As Range)
If Target.Address = "$F$5" Then

The proper call to the "Calculate" event it:

Private Sub Worksheet_Calculate()

Note how the name is "Worksheet_Calculate", and there is no parameter.
Event procedures are VERY strict. You cannot change the name or the parameters that they allow.

The issue with the Calculate event procedures is that they cannot identify which cell's value was updated/re-calculated. All that it can tell you is that some value somewhere on the sheet was re-calculated. So I don't think it is going to be helpful to you here.

What exactly is the formula in cell F5?
If it is a function of other cells whose values are hard-coded, then we can use a "Worksheet_Change" event procedure to watch for those values to change, and then do your check.

Hi,

Thanks! The formula in Cell F5 is essentially an interest rate interpolation, (it is not something I have designed so unfortunately cannot elaborate much further)... referencing both hard codes and formulas in the sheet

Cheers for your help both

 

[Joe4]

The formula in Cell F5 is essentially an interest rate interpolation

Can you post the actual formula for us to see?