msgbox when cell has value higher than 750

[CV899000]

Hi,

I am trying to prompt a message box if value entered into a cell exceeds 750.

I can do that with this code:

Set Target = Me.Range("B12")

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value > 750 Then
MsgBox "Large number, please consider"
End If
End Sub

Now, the problem is that I have a macro button in this sheet, that resets values in "B9", which then should reset the value of "B12" to "Input data".
But when I hit the reset button, the message box prompts several times before the value of "B12" changes from the high number to "input data", how can I fix that?
I do not want the message shown when I reset the cells.

 

[S_Wish]

your set of target is overrided by worksheet_change sub, so target for the procedure is each cell you select

Private Sub Worksheet_Change(ByVal Target As Range)     
If Target.Address = Range("B12").Address and Target.Value > 750 Then
        MsgBox "Large number, please consider"
    End If
End Sub

 

[CV899000]

That did a lot, but I am still not all the way.

Now when I enter a value above 750 I get the message once. Great.
But when I hit reset, I get the message again, how do I avoid that? When I hit reset, I do not need to be reminded that the value is to large, because I am in the process of resetting it.

 

[S_Wish]

you should find out another IF condition though, dunno what to suggest you)
try that may be

Dim num As Double

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("B12").Address And num < 750 And Target.Value > 750 Then
        num = Target
        MsgBox "Large number, please consider"
    End If
End Sub

but it will not work correclty i suppose

 

[CV899000]

Is it possible to put in a code that ends the msgbox sub, when I call the reset sub?

So it never begins to check the value if I have pressed the reset button?

 

[CV899000]

Now, again I do not speak the excel language yet, so please bear with me, but I imagine something like this, just in the correct code language:

Private Sub Worksheet_Change(ByVal Target As Range)
If reset sub called Then exit sub
If Target.Address = Range("B12").Address and Target.Value > 750 Then
MsgBox "Large number, please consider"
End If
End Sub

 

[S_Wish]

that one is gonna work:
when you pop >750 and until you get <750 msgbox wont appear the same(will appear new) but you can change that second IF statement to whatever you want

Dim num As Double

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("B12").Address Then
    If Target.Value > 750 And num < 750 Then
        num = Target
        MsgBox "Large number, please consider"
        Exit Sub
    End If
    If Target.Value > 750 And num > 750 Then
        num = Target
        MsgBox "Large number again! reconsider!!!"
        Exit Sub
    End If
    If Target.Value < 750 Then
    num = 0
    End If
End If
End Sub

 

[CV899000]

If I use that, I get a "Run-time error '13': Type mismatch" that leads me to the part I marked with red:

Dim num As Double

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("B12").Address Then
If Target.Value > 750 And num < 750 Then
num = Target
MsgBox "Large number, please consider"
Exit Sub
End If
If Target.Value > 750 And num > 750 Then
num = Target
MsgBox "Large number again! reconsider!!!"
Exit Sub
End If
If Target.Value < 750 Then
num = 0
End If
End If
End Sub

 

[S_Wish]

it happens when you write words there. num is a number, when you write a text in cell B12, you get that error

 

[CV899000]

I got it to work with this code:

Dim num As Double

If Target.Address = Range("B12").Address Then
If Target.Value > 750 And num < 750 Then
num = Target
MsgBox "Large number, please consider"
Exit Sub
End If
If Target.Value < 750 Then
num = 0

This is your code, but I just deleted something from it.
So thank you yet again for the help