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

 

[data808]

Firstly can you put your cursor inside the code and go Debug > Compile VBA project.
Does that show up any errors ?

If not put a breakpoint on the Application.Screenupdating (by clicking in the margin next to the line)
Change a value in the spreadsheet that will change R6.
It should trigger the code and stop at the breakpoint.
Then F8 through the code and see where it goes and if it errors out.

Oops. Sorry forgot the blinking cell was part of what we were trying to do. The flashing of the cell doesn't work obviously cause I deactivated it. But the msgbox does come out when the quota is met at 150. So working half way. Apologies for the confusion.

 

[Alex Blakenburg]

You have lost me a bit.
We were initially talking about a WorkSheet_Change event to kick in when the value exceeds 149.9 for the first time.
Firstly is that working ?

You have now mentioned a
• Workbook_Activate Event
and a
• Workbook_SheetCalculate Event
and a FLASHING_CELL, I am assuming that is similar to what is covered here, although your code looks nothing like that.
Make a Cell Blink

If the > 149.9 is working, I suggest you start a new thread for the rest. It is not something I have done before.

 

[data808]

You have lost me a bit.
We were initially talking about a WorkSheet_Change event to kick in when the value exceeds 149.9 for the first time.
Firstly is that working ?

You have now mentioned a
• Workbook_Activate Event
and a
• Workbook_SheetCalculate Event
and a FLASHING_CELL, I am assuming that is similar to what is covered here, although your code looks nothing like that.
Make a Cell Blink

If the > 149.9 is working, I suggest you start a new thread for the rest. It is not something I have done before.

Sorry @Alex Blakenburg. Had to do some digging in my posts and got mixed up with this because it's for the same spreadsheet that I'm working on. Your posts were to help with the 150 quota message which we are now successful. I have another post that someone was working with me to get the cell R6 to flash some colors when they hit 150 quota but so far it hasn't worked on the file I want it for but if I start a brand new book1.xlsm file and paste that code for the flashing R6 cell, then it works for some reason. Going to go look into that now but just wanted to say thank you so much for your help. It seems to be working for the msgbox when they hit 150.

 

[Alex Blakenburg]

Appreciate you coming back and closing this out. Glad I could help. Hopefully you get your blinking cell sorted

 

[data808]

Appreciate you coming back and closing this out. Glad I could help. Hopefully you get your blinking cell sorted

No problem and I hate to say this but, I just found something odd when I start entering data values to sum up to the 150 quota, I notice only on the first entry, the spreadsheet will flicker a split second after I enter a number and then press enter and the cursor will not drop down to the cell below. Again this only happens on the first number I enter. After that everything resumes as it should. Then I tested a button I have that does an auto SaveAs function and then I got an error message:

Run-time error '1004':
Method 'Undo' of object'_Application' failed

I looked into it and it points to this line in the Worksheet_Change event code:

Application.Undo ' Get previous value

Do you know if these two issues are related? And how you would fix it?

 

[data808]

Appreciate you coming back and closing this out. Glad I could help. Hopefully you get your blinking cell sorted

Just FYI. I deactivated the two application.undo lines and now it seems to be working:

    Application.EnableEvents = False
    If IsNumeric(Range("R6")) Then
        If gPrevAchieved = "" Then
            Application.EnableEvents = False
            'Application.Undo    ' Get previous value
            gPrevAchieved = Range("R6").Value
            'Application.Undo    ' Restore current value
        End If
        
        If gPrevAchieved <> Range("R6") Then
            If gPrevAchieved < 150 And Range("R6") > 149.99 Then
                gPrevAchieved = Range("R6").Value
                MsgBox "Congratulations!!!" & vbNewLine & vbNewLine & _
                        "You have made your quota for the day!" & vbNewLine & _
                        "Time to chillax..."
            Else
                gPrevAchieved = Range("R6").Value
            End If
        End If
    End If
        
    Application.EnableEvents = True

 

[Alex Blakenburg]

I new there was a reason I didn't like using the Undo method.

Try replacing that code with this, it stores the previous value in the name manager.
(You can also remove the Global Variable declaration from the top since this doesn't use it Public gPrevAchieved As Variant)

    Dim getPrevAchieved As Variant
    On Error Resume Next
        getPrevAchieved = [PrevAchieved]
    On Error GoTo 0
   
    If IsNumeric(Range("R6")) Then
        Application.EnableEvents = False
        If IsError(getPrevAchieved) Then
            getPrevAchieved = Me.Range("R6").Value
            Me.Names.Add Name:="PrevAchieved", RefersTo:=getPrevAchieved
            Me.Names("PrevAchieved").Comment = "Worksheet Change Event Store Value"
        End If
    
     If getPrevAchieved <> Range("R6") Then
            If getPrevAchieved < 150 And Range("R6") > 149.99 Then
                getPrevAchieved = Range("R6").Value
                Me.Names.Add Name:="PrevAchieved", RefersTo:=getPrevAchieved
                MsgBox "Congratulations!!!" & vbNewLine & vbNewLine & _
                        "You have made your quota for the day!" & vbNewLine & _
                        "Time to chillax..."
            Else
                getPrevAchieved = Range("R6").Value
                Me.Names.Add Name:="PrevAchieved", RefersTo:=getPrevAchieved
            End If
        End If
        Application.EnableEvents = True
    End If
    ' ---- End of Quota message box section ----

 

[data808]

I new there was a reason I didn't like using the Undo method.

Try replacing that code with this, it stores the previous value in the name manager.
(You can also remove the Global Variable declaration from the top since this doesn't use it Public gPrevAchieved As Variant)

    Dim getPrevAchieved As Variant
    On Error Resume Next
        getPrevAchieved = [PrevAchieved]
    On Error GoTo 0
  
    If IsNumeric(Range("R6")) Then
        Application.EnableEvents = False
        If IsError(getPrevAchieved) Then
            getPrevAchieved = Me.Range("R6").Value
            Me.Names.Add Name:="PrevAchieved", RefersTo:=getPrevAchieved
            Me.Names("PrevAchieved").Comment = "Worksheet Change Event Store Value"
        End If
   
     If getPrevAchieved <> Range("R6") Then
            If getPrevAchieved < 150 And Range("R6") > 149.99 Then
                getPrevAchieved = Range("R6").Value
                Me.Names.Add Name:="PrevAchieved", RefersTo:=getPrevAchieved
                MsgBox "Congratulations!!!" & vbNewLine & vbNewLine & _
                        "You have made your quota for the day!" & vbNewLine & _
                        "Time to chillax..."
            Else
                getPrevAchieved = Range("R6").Value
                Me.Names.Add Name:="PrevAchieved", RefersTo:=getPrevAchieved
            End If
        End If
        Application.EnableEvents = True
    End If
    ' ---- End of Quota message box section ----

I new there was a reason I didn't like using the Undo method.

Try replacing that code with this, it stores the previous value in the name manager.
(You can also remove the Global Variable declaration from the top since this doesn't use it Public gPrevAchieved As Variant)

    Dim getPrevAchieved As Variant
    On Error Resume Next
        getPrevAchieved = [PrevAchieved]
    On Error GoTo 0
  
    If IsNumeric(Range("R6")) Then
        Application.EnableEvents = False
        If IsError(getPrevAchieved) Then
            getPrevAchieved = Me.Range("R6").Value
            Me.Names.Add Name:="PrevAchieved", RefersTo:=getPrevAchieved
            Me.Names("PrevAchieved").Comment = "Worksheet Change Event Store Value"
        End If
   
     If getPrevAchieved <> Range("R6") Then
            If getPrevAchieved < 150 And Range("R6") > 149.99 Then
                getPrevAchieved = Range("R6").Value
                Me.Names.Add Name:="PrevAchieved", RefersTo:=getPrevAchieved
                MsgBox "Congratulations!!!" & vbNewLine & vbNewLine & _
                        "You have made your quota for the day!" & vbNewLine & _
                        "Time to chillax..."
            Else
                getPrevAchieved = Range("R6").Value
                Me.Names.Add Name:="PrevAchieved", RefersTo:=getPrevAchieved
            End If
        End If
        Application.EnableEvents = True
    End If
    ' ---- End of Quota message box section ----

Ok did everything you told me and its function perfectly as far as I can tell. So thank you very much for that. Do you know if the Option Explicit is needed as well?

 

[Alex Blakenburg]

Do you know if the Option Explicit is needed as well?

It depends on what you mean by needed.
To "run" the code that someone else has provided, no you don't need it.
There are a small minority of people answering questions who don't have it turned on by default and write code without consistently declaring their variables (the Dim statements you see in the code). If you try to run that code and have Option Explicit turned on at your end, the code will complain that the variables have not been declared and you will need to add Dim statements for the variables that don' have them.

To write code, the vast majority of those writing code have Option Explicit turned on by default and are basically horrified when another developer doesn't use it. It forces you to use Dim statements every time you create a new variable and will show you an error whenever you mistype a variable when you use it.
The punchiest article I have seen is this one by Russell Proctor at Better Solutions but there are plenty of articles and youtube videos demonstrating it.
VBA Variables - Option Explicit
By using the Dim statement with a data type it also stops VBA treating the variable as a variant, which slows the code down and can produce unexpected results.