PDA

View Full Version : [SOLVED] PAUSE AND WAIT EXCEL VBA



DeanP
02-12-2019, 08:43 AM
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?

Leith Ross
02-12-2019, 01:37 PM
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 SubFinal 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

Leith Ross
02-12-2019, 01:38 PM
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

DeanP
02-13-2019, 01:06 AM
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?

Leith Ross
02-13-2019, 10:56 AM
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.

DeanP
02-18-2019, 10:26 AM
Thank you!

Leith Ross
02-18-2019, 12:17 PM
Hello DeanP,

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