If R6 > 149.99 Then

[data808]

I have a R6 cell that when the user goes over 149.99 or more than it will show them a message box. However, how do I prevent it from showing once they hit 150? If the user continues to go up in numbers it will keep popping up the message box but I would only like it to show them one time once they hit their 150 quota. Here is the code I have so far:

If [R6] > "149.99" Then
MsgBox "Congratulations!!!" & vbNewLine & vbNewLine & _
"You have made your quota for the day!" & vbNewLine & _
"Time to chillax..."
End If

 

[johnnyL]

Something like:

If [R6] > "149.99" & NotBeenCompletedYet = False Then
MsgBox "Congratulations!!!" & vbNewLine & vbNewLine & _
"You have made your quota for the day!" & vbNewLine & _
"Time to chillax..."
NotBeenCompletedYet = True
End If

 

[Alex Blakenburg]

I wouldn't expect the quote marks around the 149.99
Do you have the code in a worksheet_change event ?

 

[data808]

I wouldn't expect the quote marks around the 149.99
Do you have the code in a worksheet_change event ?

Yes this code is in a worksheet_change event.

 

[Alex Blakenburg]

You would need to store the previous value of R6 somewhere. Where do you want to put that ?
We could put it in the name manager but if it gets out of sync you need to be able to maintain it.
Also do you have a sheet for each user ? or do you have something like an R6 cells for each user ?

 

[data808]

Something like:

If [R6] > "149.99" & NotBeenCompletedYet = False Then
MsgBox "Congratulations!!!" & vbNewLine & vbNewLine & _
"You have made your quota for the day!" & vbNewLine & _
"Time to chillax..."
NotBeenCompletedYet = True
End If

Still trying to experiment with this but right now it always has the msgbox pop up regardless of how much the value reflects in cell R6.

 

[data808]

You would need to store the previous value of R6 somewhere. Where do you want to put that ?
We could put it in the name manager but if it gets out of sync you need to be able to maintain it.
Also do you have a sheet for each user ? or do you have something like an R6 cells for each user ?

Not sure what name manager is.

Is it because of that =CONCATENATE(K99)*1 formula in the R6 cell? What if I changed it to just =K99 instead? Would that make it easier?
Each user will have their own .xlsm file and each of us will only use sheet1. We don't touch or even look at sheet2 or sheet3.

 

[Alex Blakenburg]

The CONCATENATE in "=CONCATENATE(K99)*1" doesn't do anything except perhaps create confusion.

No that is not the issue.
Once you change a value in a cell, excel no longer knows what the old value is so it can't tell whether the old value previously triggered the If statement or not.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim PrevAchieved As Variant
    
    If Not Intersect(Target, Range("R6")) Is Nothing _
            And Target.Cells.Count = 1 _
            And IsNumeric(Target.Value) Then
        Application.EnableEvents = False
        Application.Undo    ' Get previous value
        PrevAchieved = Target.Value
        Application.Undo    ' Restore current value
        
        If PrevAchieved < 150 And Target.Value > 149.99 Then
            MsgBox "Congratulations!!!" & vbNewLine & vbNewLine & _
                    "You have made your quota for the day!" & vbNewLine & _
                    "Time to chillax..."
        End If
        Application.EnableEvents = True
    End If

End Sub

 

[data808]

The CONCATENATE in "=CONCATENATE(K99)*1" doesn't do anything except perhaps create confusion.

No that is not the issue.
Once you change a value in a cell, excel no longer knows what the old value is so it can't tell whether the old value previously triggered the If statement or not.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim PrevAchieved As Variant
   
    If Not Intersect(Target, Range("R6")) Is Nothing _
            And Target.Cells.Count = 1 _
            And IsNumeric(Target.Value) Then
        Application.EnableEvents = False
        Application.Undo    ' Get previous value
        PrevAchieved = Target.Value
        Application.Undo    ' Restore current value
       
        If PrevAchieved < 150 And Target.Value > 149.99 Then
            MsgBox "Congratulations!!!" & vbNewLine & vbNewLine & _
                    "You have made your quota for the day!" & vbNewLine & _
                    "Time to chillax..."
        End If
        Application.EnableEvents = True
    End If

End Sub

This code didn't do anything in the change event. Everything is still running as it did before I pasted this code. Also tried it in SelectionChange event and still nothing.

 

[Alex Blakenburg]

1) in any cell type =ISNUMBER(R2) and confirm that it says True.
2) show me all the code in your worksheet module for that sheet.