Consulting

Results 1 to 7 of 7

Thread: PAUSE AND WAIT EXCEL VBA

  1. #1
    VBAX Regular
    Joined
    Nov 2018
    Location
    London, U.K.
    Posts
    85
    Location

    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.
    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?

  2. #2
    VBAX Mentor Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    405
    Location
    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
    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
    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
    Sincerely,
    Leith Ross

  3. #3
    VBAX Mentor Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    405
    Location
    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
    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
    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
    Sincerely,
    Leith Ross

  4. #4
    VBAX Regular
    Joined
    Nov 2018
    Location
    London, U.K.
    Posts
    85
    Location
    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?

  5. #5
    VBAX Mentor Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    405
    Location
    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.
    Sincerely,
    Leith Ross

  6. #6
    VBAX Regular
    Joined
    Nov 2018
    Location
    London, U.K.
    Posts
    85
    Location
    Thank you!

  7. #7
    VBAX Mentor Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    405
    Location
    Hello DeanP,

    You're welcome.. Hopefully it all made sense.
    Sincerely,
    Leith Ross

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •