Using Chip Pearsons code to try achieve a pause in the running of a module from a non-modal userform, at a certain point I need the user to verify the data on the worksheet is correct and if they A) Do nothing, it cancels out, B) They select that they want to cancel or C) They select that it is ok and it continues, but not having any luck, because the worksheet change sub never occurs,
So this is in the userform code to create the pause:
Dim B As Boolean
MsgBox "Please verify Data is correct."
B = ThisWorkbook.WaitForUserInput(WaitSeconds:=30, _
If B = True Then
Debug.Print "User Selected: " & Worksheets(1).Range("K6").Value
Debug.Print "User Select timed out, Transmittal Cancelled"
Then these two are in ThisWorkbook:
Private WatchCell As Range
Private CellChanged As Boolean
Public Function WaitForUserInput(WaitSeconds As Long, _
WaitCell As Range) As Boolean
Dim TimeStart As Double
CellChanged = False
Set WatchCell = WaitCell
TimeStart = Now
If CellChanged = True Then
WaitForUserInput = True
Loop While Now - TimeStart < TimeSerial(0, 0, WaitSeconds)
WaitForUserInput = False
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
If Not WatchCell Is Nothing Then
If Not Application.Intersect(Target, WatchCell) Is Nothing Then
CellChanged = True
So the user provides some input on a Userform, hits a button, the userform code takes off, uses that data to look up more information and populate a worksheet and then reaches the point above where it calls the Function WaitForUserInput. The function runs, the code pauses, the user can check the worksheet, but when a change is made to the worksheet, it doesn't cancel, the Private Sub Workbook_SheetChange is never activated, and so the function will always wait the full 30 seconds and always return B as false.
I tried dumping Application.EnableEvents = True into the 'Workbook_open(), made no difference.
Any ideas what the issue is? Using Office 2010, but will need this to also work in Office 2013.