Solved

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

Posted on 2015-01-28
23
341 Views
Last Modified: 2016-02-10
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.
0
Comment
Question by:Conor_Newman
  • 12
  • 11
23 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40575161
Any chance for you to upload the workbook as trying to put the pieces of the puzzle is easier with a workbook.
gowflow
0
 
LVL 2

Author Comment

by:Conor_Newman
ID: 40575193
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40575208
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
 
LVL 2

Author Comment

by:Conor_Newman
ID: 40575233
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40575235
But I notice the wait is annoying !!! why is it you need to wait for 30 seconds ??
gowflow
0
 
LVL 2

Author Comment

by:Conor_Newman
ID: 40575246
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40575258
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
 
LVL 2

Author Comment

by:Conor_Newman
ID: 40575269
Everything in the code works fine except, still, the workbook_sheetchange sub never triggers..
0
 
LVL 2

Author Comment

by:Conor_Newman
ID: 40575273
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40575331
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
 
LVL 2

Author Comment

by:Conor_Newman
ID: 40575356
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 29

Expert Comment

by:gowflow
ID: 40575375
Wait let me try something !

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

gowflow
0
 
LVL 2

Author Comment

by:Conor_Newman
ID: 40575381
Ok.

Actually the question says "Non-Modal" ;-)
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40575382
Try this then !!!
gowflow
Pause-V01.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40575385
My mistake I saw it modal !!!
try it and let me know it triggers worksheet change


gowflow
0
 
LVL 2

Assisted Solution

by:Conor_Newman
Conor_Newman earned 0 total points
ID: 40575402
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40575409
Just wait a sec
gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40575431
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
 
LVL 2

Author Comment

by:Conor_Newman
ID: 40575443
Thanks for all the help!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40575464
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
 
LVL 2

Author Comment

by:Conor_Newman
ID: 40575480
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40575681
Ah ok no problem at all !!!
I thought it was a mistake.

Glad I could help.
gowlfow
0
 
LVL 2

Author Closing Comment

by:Conor_Newman
ID: 40583577
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now