Private sub worksheet calculate Hi, I have adjusted this macro the be places ins

Question

Private sub worksheet calculate

Hi,

I have adjusted this macro the be places inside a worksheet calculate. The macro I have placed advices me if J5 is equal to the number zero, this is because I have a formula that sum's up all the amount on column H, an pop form appears and a advices me that the transaction can be archived. The problem is if J5 is zero and for example I am on another sheet and I insert manually values, text date etc on another sheet the form keeps appearing,

Is this because the private sub worksheet calculate run on the entire workbook. How could this macro be narrowed down so that it only works on the sheet I wanted to and not on the whole workbook. Also the macro is inserted within the sheet I want it to work only, but still is working on the whole workbook.

The macro is provided here;

Private Sub Worksheet_Calculate()
Dim rng As Range
Const myCell As String = "J5"

Set rng = Me.Range(myCell)

If rng.Value = 0 Then
Call MsgBox(Prompt:="Total Debits Equals To Total Credits. All Transactions Can Be Archived", _
Buttons:=vbInformation, _
Title:="Archive Process")
End If
End Sub

Explanation / Answer

.T%he Worksheet_Calculate event will occur whenever the worksheet is recalculated, even if it is not the active worksheet. You could add a check to avoid displaying the message box if the sheet with the code is not the active sheet:;'

Private Sub Worksheet_Calculate()
    Dim rng As Range
    Const myCell As String = "J5"
    ' Get out if this is not the active sheet
    If ActiveSheet.Name <> Me.Name Then Exit Sub
    Set rng = Me.Range(myCell)
    If rng.Value = 0 Then
        Call MsgBox(Prompt:="Total Debits Equals To Total Credits. All Transactions Can Be Archived", _
                    Buttons:=vbInformation, _
                    Title:="Archive Process")
    End If
End Sub

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.