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]

you are welcome)

 

[CV899000]

I may have been a little too quick there, it is not working :D

 

[S_Wish]

details?

 

[CV899000]

Now if I enter a high number, it prompts a message and I can close that. Good.

If I then want to reset the sheet, I press the reset button, but then the MsgBox "Large number again! reconsider!!!" prompts.
I do not want any msgbox to prompt when I press the reset button, can I write a code that just exits the sub instead of prompting the last msgbox?

 

[CV899000]

Easy fix actually. Now it works.

The code now looks like this:

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 = 0
Exit Sub
End If
If Target.Value < 750 Then
num = 0
End If

 

[S_Wish]

you proveded code where you deleted that if condition, well, try that

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
    If MsgBox("Large number, run reset?", vbYesNo + vbQuestion) = vbYes Then
'you can call reset also here
'Call Reset
    End If
Exit Sub
End If
    If Target.Value < 750 Then
    num = 0
    End If
End If
End Sub

 

[CV899000]

Can I be allowed to write both numbers and text in B12?

 

[S_Wish]

well you need to insert if condition for line num=target, because if target is text you`ll get error like before

 

[CV899000]

The thing is,

When I reset I want it to say "Input data" in B12, which is not allowed by num=Target.
So I get the mismatch runtime error.

How do I allow the cell to contain text, but when changed, check that the entered number is not higher than 750?

 

[S_Wish]

add one more AND in first if with msgbox
Application.WorksheetFunction.IsNumber(target)