CODEWITHSUNDEEP
vbaexcel
Luis
Luis

Reputation: 139

worksheet_calculate() is not working

I have added a private sub worksheet_calculate() in a sheet called Main. I have a value in column AP with formulas derived from other sheets and if that number is greater than value in X I want to display a message as a warning that it's over, but the code is not working any suggestions why?

Private Sub Worksheet_Calculate()
If Sheets("Main").Range("AP7").value > Sheets("Main").Range("x7").value Then
    MsgBox "You Are Over Pieces Suggested"
End If
End Sub

Upvotes: 1

Views: 2942

Answers (2)

DDuffy
DDuffy

Reputation: 413

Try this. 

Private Sub Worksheet_Calculate()
If Range("AP7").Value > Range("X7").Value Then
MsgBox "You Are Over Pieces Suggested."
End If
End Sub

EDITED#### Edited the original code to run as a Worksheet_Calculate rather than a Change. Working on trying to set the ranges to columns for you now.

EDIT#########

I flippin love a challenge. Try This.

Private Sub Worksheet_Calculate()

Set Target = Range("AP:AP").SpecialCells(xlCellTypeFormulas)
If Target Is Nothing Then Exit Sub
    For Each c In Target
        If c > Range("X" & c.Row) Then
            MsgBox "You Are Over Pieces Suggested - Cell " & "AP" & c.Row
        End If
    Next

End Sub

Upvotes: 1

Dory Owen
Dory Owen

Reputation: 67

Consider using Data Validation on cell AP7 using a "Custom" formula of: =AP7<=$X$7 Fill in the Error Alert tab on the validation menu: Stop; "You Are Over Pieces Suggested". I think this might achieve what you want without any macros. In fact, it can prevent an invalid number from being entered in the first place.

Upvotes: 0

Related Questions