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]

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.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

' type in columns A-C to do a time stamp in column D. also capital HH:MM is for military time. hh:mm would be _
regular time so you could include am/pm. Example "hh:mm am/pm"
    With Target
        If .Count > 1 Then Exit Sub
        If Not Intersect(Range("A:C"), .Cells) Is Nothing Then
        Application.EnableEvents = False
        With Range("D" & Target.Row)
            If Not IsDate(.Value) Then
                .NumberFormat = "HH:MM"
                .Value = Now
            End If
        End With
        Application.EnableEvents = True
        End If
    End With
' same as above but different range and target
        With Target
        If .Count > 1 Then Exit Sub
        If Not Intersect(Range("F:O"), .Cells) Is Nothing Then
        Application.EnableEvents = False
        With Range("E" & Target.Row)
            If Not IsDate(.Value) Then
                .NumberFormat = "HH:MM"
                .Value = Now
            End If
        End With
        Application.EnableEvents = True
        End If
    End With
' takes first 3 characters from the drop down list in column C. drop down list was done by data validation
    If Not Intersect(Target, Range("C8:C88")) Is Nothing And Target.CountLarge = 1 Then
        Application.EnableEvents = False
        Target = Left(Target, 3)
        Application.EnableEvents = True
    End If

End Sub

 

[Alex Blakenburg]

I don't understand, I can't see any reference to R6 nor can I see any of the code I gave you included ?

 

[data808]

I don't understand, I can't see any reference to R6 nor can I see any of the code I gave you included ?

Yeah this is what was in the change event before I included your code. I can add that in if you'd like. I tried your code at the top and at the bottom on 2 separate occasions and both did not trigger anything to happen.

Also you are correct, there is no reference in the VBA to R6. I have other things going on with R6 like =K99 in the formula text box and also a conditional formatting which colors the numeric font in the R6 cell starting with red from values 0-149.99 and turning green after it hits 150 and up. Also not sure if you forgot but R6 is actually a merged cell with R7 just fyi. Not sure if this would cause any problems either.

Thank you very much for the help btw. Really appreciate you taking the time to try and figure this out.

 

[Alex Blakenburg]

I can't say that I have seen any mention of merged cells before this but I tested it and it works fine.
I also tested it at the start of your code and that works too.

I don't believe you have come back to me on

=ISNUMBER(R6)

returns, especially after you put in a value you believe should have triggered the code.

 

[data808]

I can't say that I have seen any mention of merged cells before this but I tested it and it works fine.
I also tested it at the start of your code and that works too.

I don't believe you have come back to me on

=ISNUMBER(R6)

returns, especially after you put in a value you believe should have triggered the code.

I just tested it on a brand new file book1.xlsm

I tried to setup the environment with the key cells. Merged R6 with R7 and made it =K99

Then I pasted your code into the change event of the worksheet and then started to type random numbers into K99 cell. Once I typed a number at 150 or higher, no msgbox pops up. Basically nothing happens. Am I missing something?

Not sure what you want me to do with the =ISNUMBER(R6)? Did you want me to type that into the formula text box for cell R6?

 

[Alex Blakenburg]

You can't type a formula into R6 that refers to R6.
The code won't work if R6 is not being recognised as a number.
In any cell in the sheet put that formula and confirm that it is being recognised as a number.

 

[data808]

You can't type a formula into R6 that refers to R6.
The code won't work if R6 is not being recognised as a number.
In any cell in the sheet put that formula and confirm that it is being recognised as a number.

Ok got it. It says TRUE in the cell after typing that.

 

[Alex Blakenburg]

I am going to need a copy of the code with the modifications included and an XL2BB that includes R6 and the K cell it seems to be dependant on.

 

[data808]

I am going to need a copy of the code with the modifications included and an XL2BB that includes R6 and the K cell it seems to be dependant on.

I’m knocking off for today as I need to get to bed. Need to work tomorrow but would you be able to post the working code one more time just to be sure we have your same code since you say yours works. I just find it odd that even on a blank spreadsheet I couldn’t get it to work. I’ll test it again tomorrow and let you know. Thanks again!

 

[Alex Blakenburg]

I haven't touched your code just added what I gave you to the beginning.
If you are testing it on a empty sheet. You would need to:
• Put the code in the Sheet module of that sheet.
• Merge R6 and R7
• Key in values under $ 150 into R6 and then key in a value over 150

Private Sub Worksheet_Change(ByVal Target As Excel.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

' type in columns A-C to do a time stamp in column D. also capital HH:MM is for military time. hh:mm would be _
regular time so you could include am/pm. Example "hh:mm am/pm"
    With Target
        If .Count > 1 Then Exit Sub
        If Not Intersect(Range("A:C"), .Cells) Is Nothing Then
        Application.EnableEvents = False
        With Range("D" & Target.Row)
            If Not IsDate(.Value) Then
                .NumberFormat = "HH:MM"
                .Value = Now
            End If
        End With
        Application.EnableEvents = True
        End If
    End With
' same as above but different range and target
        With Target
        If .Count > 1 Then Exit Sub
        If Not Intersect(Range("F:O"), .Cells) Is Nothing Then
        Application.EnableEvents = False
        With Range("E" & Target.Row)
            If Not IsDate(.Value) Then
                .NumberFormat = "HH:MM"
                .Value = Now
            End If
        End With
        Application.EnableEvents = True
        End If
    End With
' takes first 3 characters from the drop down list in column C. drop down list was done by data validation
    If Not Intersect(Target, Range("C8:C88")) Is Nothing And Target.CountLarge = 1 Then
        Application.EnableEvents = False
        Target = Left(Target, 3)
        Application.EnableEvents = True
    End If

End Sub