Solved

vba - Excel - userform - countdown delay OR OK/Cancel

Posted on 2012-03-16
12
875 Views
Last Modified: 2012-08-13
Hi experts,

I'd like to show a message at the beginning of a macro that will allow the user to Abort (or continue) immediately.
However, if the user is not there, I want the execution to continue after let's say 15 seconds. Of course, I would like this message to show a countdown for the user to know how many seconds are remaining for a potential abort before its too late.

Any suggestion?
Thanks in advance!
0
Comment
Question by:clemex
  • 7
  • 4
12 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37731205
I believe I've already developed what you need in a past solution.  The userform goes up and the user has a chance to cancel, but after so many seconds, the process continues the automation.

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_27454496.html

Let me know if I can be of further assistance.

Cheers,

Dave
0
 

Author Closing Comment

by:clemex
ID: 37738775
Would be excellent with a countdown or a progress bar indicating the time remaining to cancel.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37738836
Then, why did you close the question out?  Do you need a progress bar (I must have missed that part)?

Dave
0
 

Author Comment

by:clemex
ID: 37738953
Sorry, but I wrote: "... I would like this message to show a countdown for the user to know how many seconds are remaining for a potential abort before its too late." Your solution was perfect for the first part but this part was missing. As I needed to deliver the macro right away, I did a rapid search elsewhere and found something to replace the countdown, a progress bar which is not as good but does the job.
Myriam
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37738957
I have the countdown for you now.  Do you want it?

Dave
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:clemex
ID: 37739013
Of course! I would use it next time I will have to make changes to this macro or in another macro, it can always be useful. Is there a way I can change the Good to Excellent? Next time, I will wait a bit before closing a question.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37739073
No worries, I appreciate you wanting exactly what you asked for (and barring a reason to provide that, I would recommend probing the Expert as he - at least I - may not have caught 100% of what you needed).

You can request attention and then regrade as you choose.

Give me a moment, I'm testing and prettying it up a bit....

;)

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37739196
Ok - thanks for your patience.

You have the option to set Hours, Mins, and Seconds on the wait (I added a bit more sophistication to give you a better app).  There are two timers running - one to "continueAutomation" and one to update the progress in the form of a countdown clock.

Here's the code in a public module:
Option Explicit

Public timeDisplay As Date
Public timeStarted As Date
Public timeContinueAutomation As Date
Public bAbort As Boolean
Public bContinue As Boolean
Public runWhen As Double

Public Sub getItGoing()
Dim waitHours As Integer
Dim waitMins As Integer
Dim waitSecs As Integer

    bAbort = False
    bContinue = False
    
    waitHours = 0
    waitMins = 1
    waitSecs = 10

    timeDisplay = TimeSerial(waitHours, waitMins, waitSecs)
    UserForm1.lblCountDown = Format(timeDisplay, "HH:MM:SS")
    
    timeStarted = Now()
    timeContinueAutomation = Now() + TimeSerial(waitHours, waitMins, waitSecs) 'How much time until the process continues the automation
    
    Application.OnTime earliesttime:=timeContinueAutomation, procedure:="continueAutomation", schedule:=True
    Application.OnTime earliesttime:=Now() + TimeSerial(0, 0, 1), procedure:="updateCountDownLabel", schedule:=True
    
    Load UserForm1
    UserForm1.Show
End Sub
Sub updateCountDownLabel()
Dim cntDown As String

    If Not bAbort And Not bContinue Then
        'timeDisplay = timeDisplay - TimeSerial(0, 0, 1)
        timeDisplay = timeContinueAutomation - Now()
        UserForm1.lblCountDown = Format(timeDisplay, "HH:MM:SS")
        runWhen = Now() + TimeSerial(0, 0, 1)
        'Debug.Print "scheduling " & runWhen
        Application.OnTime earliesttime:=runWhen, procedure:="updateCountDownLabel", schedule:=True
    Else
        Call stopTimerUpdateCountDownLabel
    End If
End Sub
Sub stopTimerUpdateCountDownLabel()
        On Error Resume Next
        'Debug.Print "cancelling " & runWhen
        Application.OnTime earliesttime:=runWhen, procedure:="updateCountDownLabel", schedule:=False
        On Error GoTo 0
End Sub
Public Sub continueAutomation()
    Unload UserForm1
    If Not bAbort And Not bContinue Then
        Call macroToRun
    End If
End Sub

Sub macroToRun()
    bContinue = True
    MsgBox "You're now running the automated open code...", vbOKOnly, "not really - just a prompt for example" '<- replace this with your code that runs after 5 seconds, or a call to that subroutine
End Sub

Open in new window


And here's the userform code:
Option Explicit

Private Sub cbCancel_Click()
    bAbort = True
    Unload UserForm1
End Sub

Private Sub cbContinue_Click()
    Unload UserForm1
    Call macroToRun
End Sub

Open in new window


See attached demonstration workbook.

Cheers,

Dave
countDownTimer-r2.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37739837
Please let me know when you get a chance if this is working well for you.

Cheers,

Dave
0
 

Author Comment

by:clemex
ID: 37741638
This is exactly what I needed!

Thank you Dave,
Myriam
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37742902
Glad you liked it.  If you want to award/recognize my final post, you can request attention and the moderator will assist from there.

Dave
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

863 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

21 Experts available now in Live!

Get 1:1 Help Now