MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Procedure to abort automatic procedure?

  Asked By: Elliott    Date: Dec 19    Category: MS Office    Views: 1005

I have created a macro that downloads data from the web. Since this
data on the website is updating constantly, I need to rerun my
download macro every hour or every 30 minutes or whatever I ultimately

I am using Windows Scheduled Tasks to open the workbook at the desired
intervals; and upon opening, the macro is executed.

My goal here is to have the entire procedure repeat itself
automatically for hours on end if I am not available.

Because of an obnoxious "read only" popup that I can't get around, I
cannot automatically run my procedure unless Excel is closed at the
time of the scheduled event.

For this reason I have added another procedure that saves the workbook
and closes Excel 5 minutes after the workbook is opened (so that it
will be closed when the time comes for the next execution).

It works better than it sounds--Everything works as desired up to this

My question deals with the situation which occurs when it comes time
for the scheduled task AND I am working with Excel AND I do not want
Excel to close automatically.

I would like to insert an Input Box which gives me the option of
aborting the "automatic Excel close" and if I have not made the
appropriate response within, say, 20 seconds, then the automatic
procedure goes ahead and does its thing.

To this end, I have gotten this far:

Dim Msg As String, Title As String
Dim Config As Integer, Ans As Integer
Msg = "Unless you intervene now, ALL EXCEL WILL CLOSE in 2 Minutes"
Msg = Msg & " with no further warnings and you will LOSE any"
Msg = Msg & " unsaved material."
Msg = Msg & vbNewLine & vbNewLine
Msg = Msg & "Do you want to STOP EXCEL FROM CLOSING?"
Title = "Excel About to Close Automatically!"
Config = vbYesNo + vbCritical + vbDefaultButton2
Ans = MsgBox(Msg, Config, Title)
If Ans = vbYes Then
Exit Sub
Else: Application.OnTime Now + TimeValue("00:02:00"), "EndShow"
End If


My problem, of course, is that the procedure as written requires a yes
or no answer before it will do anything; whereas what I want it to do
is assume a "no" answer after 20 seconds if a "yes" answer has not
been entered.



4 Answers Found

Answer #1    Answered By: Jawahir Burki     Answered On: Dec 19

First answer off the top of my head (and there are undoubtedly better ones -
heads and answers) is to use a userform instead of a messagebox and make it
non modal. If the UserForm button click sets a public variable and closes
itself, the main macro  can test that variable. (Don't forget to set it back
when the next instance comes up).

I wonder, however if you could run  the macro in the open  instance of excel  if
there is one so avoiding the readonly box  on reopening personal.xls. This
should happen if you tell the scheduler to open the file (.xls) rather than to
open excel and run the file. Not sure if this helps though.

Answer #2    Answered By: Pearl Garza     Answered On: Dec 19

Yes, create a User Form and have it do a 20 second count down. If you do
not click the button within  20 seconds  your automated program can start.
I did the same thing  for an automated Access program. The 20 second
count down form allows me to enter the program without having it run  so
I can do work on it, etc. Here is what you will need to count down for
20 seconds.

I use a non visible label to store the value when I click on the button.
So be sure to add twp labels and a button to your form. One label shows
the 20 second count down, the other is checked by the program to see if
the cancel button has been clicked.

'Allows user time  to cancel automated process
Dim vSecond As Variant
Dim newHour, newMinute, newSecond, waitTime As Variant
Dim i As Integer

lblStop.Caption = 0
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 20
vSecond = Second(Now())
waitTime = TimeSerial(newHour, newMinute, newSecond)

'This shows a count down timer on the form
i = 20
Do Until Time > waitTime
If lblStop.Caption = 1 Then 'Check hidden label to see if button was
cmdStop.Caption = "Cancelled"
Exit Sub
End If
If vSecond = Second(Now()) Then 'The code runs faster than one second
so it takes a few iterations before one second actually passes.
vSecond = Second(Now())
i = i - 1
lblTime.Caption = i
End If

Answer #3    Answered By: Finn Jones     Answered On: Dec 19

1.'Push the Freakin Button' may solve your "read only" popup  issue.
Hard to say  without seeing it.

Older versions were freeware. Now shareware.

2. Could you use a CheckBox that your procedure  checks? : If it's
checked, shut down, else leave excel  running. Or vice versa. I might
use OnTime to ring a warning, say thirty seconds  preceding the shut
down (so that you could have the opportunity to change your mind).

Answer #4    Answered By: Ramon Davis     Answered On: Dec 19

Thanks for the responses. You've given me some interesting ideas to
look into--in the meantime I fiddled some more after a good night's
sleep and ended up what you see below, which works!!!!!!!!!

I really can't tell you how much I appreciate this site. Whether or
not I am sophisticated enough to use the suggestions that you folks
offer, the very act of writing out my request for help seems to help
me to come up with solutions by myself.
Sub Macro1()
Application.OnTime Now + TimeValue("00:01:00"), "EndShow"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "<--------"
ActiveCell.Offset(1, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = " To Stop Excel from Closing Automatically"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "in ONE MINUTE, Hit x Enter above."
ActiveCell.Offset(-2, 0).Range("A1").Select
End Sub
Sub Endshow()
If ActiveCell.Range("A1").Row <> 1 Then ActiveCell.Offset(-1, 0).Select
If ActiveCell.Value <> "x" Then
Application.DisplayAlerts = False
End If
End Sub

Didn't find what you were looking for? Find more on Procedure to abort automatic procedure? Or get search suggestion and latest updates.