Pause VBA code on Non-Modal Userform pending user input on Worksheet

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, _
        WaitCell:=Worksheets(1).Range("K6"))
    If B = True Then
        Debug.Print "User Selected: " & Worksheets(1).Range("K6").Value
    Else
        Debug.Print "User Select timed out, Transmittal Cancelled"
    End If

Open in new window


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
    Do
        DoEvents
        If CellChanged = True Then
            WaitForUserInput = True
            Exit Function
        End If
    Loop While Now - TimeStart < TimeSerial(0, 0, WaitSeconds)
    WaitForUserInput = False
End Function

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
        End If
    End If
End Sub

Open in new window


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.
LVL 2
Conor_NewmanAsked:
Who is Participating?
 
gowflowConnect With a Mentor Commented:
You need to use a difrent function

Application.Ontime

Let me know if you need help to use it. it act as a timer and will trigger a certain Macro once time ellapses
gowflow
0
 
gowflowCommented:
Any chance for you to upload the workbook as trying to put the pieces of the puzzle is easier with a workbook.
gowflow
0
 
Conor_NewmanAuthor Commented:
hmm, it'll take a while, I'll have to strip out a lot of company stuff from the code (accessing email accounts, VPN network locations etc for data) and then dump in some fake data so it will actually run at all  ;-)

WIll post it up as soon as I can
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
gowflowCommented:
its ok I made up a file but something is missing I will post it and just modify so it looks at least like what you are trying to do.

here it is.
gowflow
Pause.xlsm
0
 
Conor_NewmanAuthor Commented:
OK that works in your sheet perfectly, plugging it into mine now to see if it works there or my issue is other code blocking it.
0
 
gowflowCommented:
But I notice the wait is annoying !!! why is it you need to wait for 30 seconds ??
gowflow
0
 
Conor_NewmanAuthor Commented:
The wait is optional, it gives them time to look through the data, if they are happy, they make the change in cell K6 (A drop down) and that instigates the change sheet sub which cancels the wait and proceeds immediately. If they do just leave it and do nothing in the 30 seconds, it cancels the operation.  Once its working I'll increase the wait to 60 sec. SO they have time to read.
0
 
gowflowCommented:
Well check it out I noticed it was giving me K6 but could not see where it was set ! maybe did not give it too much time to look.

gowflow
0
 
Conor_NewmanAuthor Commented:
Everything in the code works fine except, still, the workbook_sheetchange sub never triggers..
0
 
Conor_NewmanAuthor Commented:
As you mentioned, the wait is annoying, that's the only issue, everything else works, if I could just get it to cancel the wait when K6 is changed it would be perfect.
0
 
gowflowCommented:
well when I press on the form's button Pause it hide the workbook but I cannot change cells you can move in cells but if you try to change something it won't let you. I cannot guess what your whole code is all about. this is the reason why worksheet_change doesn't trigger.

Can you pls explain why u want worksheet change to trigger is the user able to modify code ?
gowlfow
0
 
Conor_NewmanAuthor Commented:
The reason you can't change cells is your Userform is Modal=true (You can change this in the userform properties), if you have it set to non modal you can edit the worksheet while the Userform is up.

The system basically takes minimal input from a user, then draws from that a list of documents, a list of users, then assembles an email to those users and sends the latest revision of those documents to them. It all works fine, been using it for about 2 years now.

However, several times a user has input his data and the system has fired off the wrong documents because in one of the other workbooks it gets info from a mistake was made. So, I have the pause in place to give the user time to have a quick look it is as it should be, and then he/she can select from a  drop down in K6 to proceed or not.

I had thought of just putting a vbyesno pop up after a 60 seconds pause to decide whether to proceed or not, but this would mean they have no way of cancelling the pause when they are finished checking, and have to wait the full minute. Very frustrating, especially when the system was designed to speed up the process ;-)

My only issue is the workbook_sheetchange not triggering, I might try and see if I can use a Worksheet_Change on the worksheet instead. But I have a feeling the same issue may follow me.
0
 
gowflowCommented:
Wait let me try something !

I had put it modal coz you said in your question the form is modal

gowflow
0
 
Conor_NewmanAuthor Commented:
Ok.

Actually the question says "Non-Modal" ;-)
0
 
gowflowCommented:
Try this then !!!
gowflow
Pause-V01.xlsm
0
 
gowflowCommented:
My mistake I saw it modal !!!
try it and let me know it triggers worksheet change


gowflow
0
 
Conor_NewmanConnect With a Mentor Author Commented:
ok, it seems it will run when nothing else is running (Like the way you have it open the userform) but if the user form is running, the WaitForUserInput is running, it won't trigger.

So if the Function is called it can't then run the Change event code. Maybe I should forget the change event and have the Function timer loop check the value of K6 on each pass and if its changed, proceed? And just forget the Change Event code altogether?
0
 
gowflowCommented:
Just wait a sec
gowflow
0
 
Conor_NewmanAuthor Commented:
Thanks for all the help!
0
 
gowflowCommented:
here is some insight on it.

Private Sub Workbook_Activate()
StartTimer
End Sub

and make sure your macro is prefixed by "ThisWorkbook" like:
Application.OnTime EarliestTime:=RunWhen, Procedure:="ThisWorkbook.NAMEOFYOURMACRO",

============================================================================


Sub StartTimer()
    ' run it at 8pm
    RunWhen = TimeSerial(20,0,0)
    Application.OnTime EarliestTime:=RunWhen, Procedure:="your_macro_name_here", _
        Schedule:=True
End Sub
=============================================================================

Application.OnTime EarliestTime:=RunWhen, Procedure:="'" & thisworkbook.name & "'!your_macro_name_here", _
        Schedule:=True


BTW why did you close the question like this. You should not point to your name or else it will take 4 days to close !!!
If you want I can cancel the closure and you can close again only select the correct answer and not ticking your's

gowlfow
0
 
Conor_NewmanAuthor Commented:
Because both solutions worked, yours is the better solution in my case as the workbook is heavy on resources as it is, the loop I suggested is a simple way for a smaller workbook or more basic macro to achieve the same goal. I wanted people reading back on this to see both options, but I assigned all the points to you as that was the solution that helped me.
0
 
gowflowCommented:
Ah ok no problem at all !!!
I thought it was a mistake.

Glad I could help.
gowlfow
0
 
Conor_NewmanAuthor Commented:
gowflow's solution is far cleaner than the loop value check I suggested and uses less resources so is a better solution. Thanks for all the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.