Thread: Message box macro not working

I have the following code that I want to have a message box pop up if the value of cell H104 is more or less than the value of cell F104 by more than 0.02. I have been playing with this code for about 3 days now but can't seem to get it to work. Any Ideas?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Cell As Range
If Application.Intersect(Target, Range("H104")) Is Nothing Then Exit Sub
For Each Cell In Range("H104")    
If (Cell.Value < (Range("F104").Value - 0.02)) Then
       MsgBox "SCALE CHECK 1 HAS FAILED CALIBRATION AND MUST BE RECALIBRATED BEFORE IT CAN BE USED TO TAKE WEIGHTS"
             Exit Sub
        End If
     Next Cell
 If Application.Intersect(Target, Range("H104")) Is Nothing Then Exit Sub
  For Each Cell In Range("H104")  
 If (Cell.Value > (Range("F104").Value + 0.02)) Then
       MsgBox "SCALE check 1 HAS FAILED CALIBRATION AND MUST BE RECALIBRATED BEFORE IT CAN BE USED TO TAKE WEIGHTS"
              Exit Sub
        End If
Next Cell
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    Set c = Range("F104")
    If Target.Count > 1 Then Exit Sub
    If Target.Address = "$H$104" Then
        If Target - c > 0.02009 Or Target - c < -0.02009 Then
            MsgBox "Do something else"
        End If
    End If
End Sub

Or possibly.......?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    If Target.Address <> "$H$104" Then Exit Sub
    
    Select Case Target
        Case Is > Range("F104") + 0.02, Is < Range("F104").Value - 0.02
            MsgBox "SCALE CHECK 1 HAS FAILED CALIBRATION AND MUST BE RECALIBRATED BEFORE IT CAN BE USED TO TAKE WEIGHTS", vbCritical
        Case Else
            MsgBox "SCALE CHECK 1 PASSED", vbInformation
    End Select
    
End Sub

[EDIT]

I think it was how you defined your tolerances that was causing the problem.
This code should be okay now.

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

Or ...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Address <> "$H$104" Then Exit Sub
    
    If Abs(Target.Value - Me.Range("F104").Value) <= 0.02 Then
        MsgBox "All good", vbInformation
    Else
        MsgBox "Not so good", vbCritical
    End Select
End Sub

Where are you putting the code?


Demo workbook attached

[EDIT]
Code amended to allow for Delete

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    If Target.Address <> "$H$104" Then Exit Sub
    If Target = "" Then Exit Sub
    
    Select Case Target
        Case Is > Range("F104") + 0.02, Is < Range("F104").Value - 0.02
            MsgBox "SCALE CHECK 1 HAS FAILED CALIBRATION AND MUST BE RECALIBRATED BEFORE IT CAN BE USED TO TAKE WEIGHTS", vbCritical
        Case Else
            If Target <> "" Then MsgBox "SCALE CHECK 1 PASSED", vbInformation
    End Select
    
End Sub

Attachment updated

[EDIT]
See shgs' post #8

If the code is in the module ThisWorkbook

Then it will work as it stands in any sheet in the workbook.

If you only want it to apply to a specific sheet then the code must go in the module for that sheet.

Thanks shg

The Workbook_SheetChange event has to go in ThisWorkbook.

If you change the signature to

Private Sub Worksheet_Change(ByVal Target As Range)

... then it goes in the Sheet module.

hmmm ......

At first glance your code is somewhat over complicated, the continual use of

Exit Sub

means in all probability the sub has done just that before it gets to the code you are trying to add.

How much off your code actually comes into use? I suspect there will be other chunks that are not triggered.

Try putting the new code at the beginning of your code to see what I mean, then only it will work!!!!

You need at the very the least to use If > ElseIf > Else statements, or better still Select Case.

Are there other sheets in the workbook, if so the code you are using is triggered on every one!
I again am only guessing at this stage, but I think the Sheet_Change is a better place for your code.

Why do you have

Sub Workbook_BeforeClose(Cancel As Boolean)

in a stndard module, Module3, and not in the module ThisWorkbook ?

I'll have a further look later, but this will take some time to sort out.


[EDIT]

Why so many merged cells?
They are a major source of problems in my opinion

I just placed SHG's code in the worksheet module, and it worked fine

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Address <> "$H$104" Then Exit Sub

    If Abs(Target.Value - Me.Range("F104").Value) <= 0.02 Then: MsgBox "All good", vbInformation: Else: MsgBox "Not so good", vbCritical

End Sub

They all do!

The problem seems to be the amount of unneeded code, esp Exit Sub
The code has been inserted at the end of the procedure and is is being exited before it is triggered, due to other events being triggered with the change event.

Scott
Do you really need so many cells checked and changed if only one cell is changing?
This will slow excel down considerably.

Put the chosen code at the beginning and modify the conditions to trigger it and all is okay

    If Target.Address = "$H$104" Then
        If Target = "" Then Exit Sub
        
        Select Case Target
            Case Is > Range("F104") + 0.02, Is < Range("F104").Value - 0.02
                MsgBox "SCALE CHECK 1 HAS FAILED CALIBRATION AND MUST BE RECALIBRATED BEFORE IT CAN BE USED TO TAKE WEIGHTS", vbCritical
            Case Else
                MsgBox "SCALE CHECK 1 PASSED", vbInformation
        End Select
        Exit Sub
    End If

What are the tolerances required in L104,P104,T104 & X104?
I suspect the will use the same or similar code to H104


Amended workbook attached.

I'm trimming some of the code in another copy of the file and will post it later.

I hope this helps in the meantime.

It seems that there really is to much code in the workbook.'I put all of the codes in the worksheet_Change in code for worksheet and they all seemed to work. I ended up going with this code for now.

Private Sub Worksheet_Change(ByVal Target As Range)

 If Application.Intersect(Target, Union(Range("H104"), Range("L104"), Range("P104"), Range("T104"), Range("X104"))) Is Nothing Then Exit Sub
       If Range("H104").Value < Range("F104").Value - 0.02 Or Range("H104").Value > Range("F104").Value + 0.02 Then
       MsgBox "SCALE Check 1 HAS FAILED CALIBRATION AND MUST BE RECALIBRATED BEFORE IT CAN BE USED TO TAKE WEIGHTS"
       End If
       If Range("L104").Value < Range("J104").Value - 0.02 Or Range("L104").Value > Range("J104").Value + 0.02 Then
       MsgBox "SCALE Check 2 HAS FAILED CALIBRATION AND MUST BE RECALIBRATED BEFORE IT CAN BE USED TO TAKE WEIGHTS"
       End If
       If Range("P104").Value < Range("N104").Value - 0.02 Or Range("P104").Value > Range("N104").Value + 0.02 Then
       MsgBox "SCALE Check 3 HAS FAILED CALIBRATION AND MUST BE RECALIBRATED BEFORE IT CAN BE USED TO TAKE WEIGHTS"
       End If
       If Range("T104").Value < Range("R104").Value - 0.02 Or Range("T104").Value > Range("R104").Value + 0.02 Then
       MsgBox "SCALE Check 4 HAS FAILED CALIBRATION AND MUST BE RECALIBRATED BEFORE IT CAN BE USED TO TAKE WEIGHTS"
       End If
       If Range("X104").Value < Range("V104").Value - 0.02 Or Range("X104").Value > Range("V104").Value + 0.02 Then
       MsgBox "SCALE Check 5 HAS FAILED CALIBRATION AND MUST BE RECALIBRATED BEFORE IT CAN BE USED TO TAKE WEIGHTS"
       End If
           
End Sub

I'm still not really sure why its not working in the workbook as I removed all the exit subs and did not get the procedure to large worning but it still would not work when I copied and pasted it there. One thing i've learned there are alot of different ways to write code to get the same end result. And alot of it seems to be personal prefrence. Thanks to everyone for there assistance. You have all been very helpful.