PAUSE AND WAIT EXCEL VBA

I have a macro that checks whether the sum of a range = 0. If it does not equal zero a message box will ask user to check values in range. However, I also want the macro to pause and wait for values to change, then re-apply the if statement test and if the sum is zero then continue with the rest of the macro.

I have the code for the 2 message boxes, but can't find anything that makes sense for the macro to pause if sum not zero.

Code:

---

Sub FinChk()
Dim wsJournal As Worksheet
Dim VDRow As Range

    Set wsJournal = Worksheets("Journal")
    With wsJournal
   
        Set VDRow = Worksheets("Journal").Range("R7:R" & Cells(Rows.Count, "R").End(xlUp).Row)
       
            If Application.WorksheetFunction.Sum(VDRow) <> 0 Then
                MsgBox "Journal does not balance. Check column R."
                    Else
               
            If Application.WorksheetFunction.Sum(VDRow) = 0 Then
                MsgBox "Task completed! Please post."
                    End If
                End If
            End With
       
        Worksheets("AssJnl").Delete
        Worksheets("SPSJnl").Delete
        Worksheets("VolJnl").Delete
       
    End Sub

---

Any advice?

Hello DeanP,This takes two macros. One will be the Worksheet Change event for "Journal" and the second is your original with some modifications. Journal Worksheet Change Event

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)        Dim RngBeg As Range            Set RngBeg = Range("R7")                If Target.Column = RngBeg.Column And Target.Row >= RngBeg.Row Then            Set Rng = Range(RngBeg, Target)            Call FinChk        End If            End Sub

---

Final Check Macro

Code:

---

Global bCheckColR  As BooleanGlobal Rng          As RangeSub FinChk()    Dim RngBeg  As Range    Dim RngEnd  As Range    Dim Wks    As Worksheet            Set Wks = Worksheets("Journal")        Set RngBeg = Wks.Range("R7")                If Rng Is Nothing Then            Set RngEnd = Wks.Cells(Rows.Count, RngBeg.Column).End(xlUp)            If RngEnd.Row < RngBeg.Row Then Exit Sub            Set Rng = Wks.Range(RngBeg, RngEnd)        End If                If Application.WorksheetFunction.Sum(Rng) = 0 Then            bCheckColR = False            MsgBox "Task completed! Please post."                                Worksheets("AssJnl").Delete            Worksheets("SPSJnl").Delete            Worksheets("VolJnl").Delete        Else            bCheckColR = True            MsgBox "Journal does not balance. Check column R."        End If                End Sub

---

Hello DeanP,

This takes two macros. One will be the Worksheet Change event for "Journal" and the second is your original with some modifications.


Journal Worksheet Change Event

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim RngBeg As Range
   
        Set RngBeg = Range("R7")
       
        If Target.Column = RngBeg.Column And Target.Row >= RngBeg.Row Then
            Set Rng = Range(RngBeg, Target)
            Call FinChk
        End If
           
End Sub

---

Final Check Macro

Code:

---

Global bCheckColR  As Boolean
Global Rng          As Range

Sub FinChk()

    Dim RngBeg  As Range
    Dim RngEnd  As Range
    Dim Wks    As Worksheet
   
        Set Wks = Worksheets("Journal")
        Set RngBeg = Wks.Range("R7")
       
        If Rng Is Nothing Then
            Set RngEnd = Wks.Cells(Rows.Count, RngBeg.Column).End(xlUp)
            If RngEnd.Row < RngBeg.Row Then Exit Sub
            Set Rng = Wks.Range(RngBeg, RngEnd)
        End If
       
        If Application.WorksheetFunction.Sum(Rng) = 0 Then
            bCheckColR = False
            MsgBox "Task completed! Please post."
                   
            Worksheets("AssJnl").Delete
            Worksheets("SPSJnl").Delete
            Worksheets("VolJnl").Delete
        Else
            bCheckColR = True
            MsgBox "Journal does not balance. Check column R."
        End If
               
End Sub

---

Hi Leith,
Thank you veru much for your response. I am a bit in the dark as to what the first macro does?
These 2 macros together with a bunch of others all form part of one process to create a journal. Each macro calls another in sequence of steps that have to be performed to get to the eventual result. The new Worksheet_Change macro calls the FinChk macro (which is the last macro in the sequence), but I'm not sure now what the macro that called FinChk before must do now?

Hello DeanP,

Here is what is happening. The Worksheet Change macro handles the delayed entry. Once the information has been entered by the user into column "R" and is checked, the global Boolean variable bCheckColR is set to True when the column does not balance. The FinChk macro then must wait for the user to user to make the changes. The Worksheet Change macro is triggered when a cell's value changes/ The macro checks if the cell is in column "R" and if it is row 7 or greater and if the Boolean variable bCheckColR is True. If all these conditions are met then FinChk is called again to check if the balance in column "R" is correct. If it is then bCheckColR is set to False and the code continues to the next macro. If not then the cycle will repeat.

Thank you!

Hello DeanP,

You're welcome.. Hopefully it all made sense.