Macro Ask User Input, Proceed if Ignored

DBCox

Board Regular
Joined
Jul 5, 2012
Messages
52
Hello everyone,

I have a workbook with a macro that runs automatically when the file is opened. It works great, except that the process takes quite some time due to the size of the file and the scripts it triggers. I have Windows Scheduler setup to open (thus run) the worksheet at a certain time each day. Occasionally I need to do maintenance to the file and do not want it to automatically start running all of the time consuming scripts and functions.

The workbook is extremely large and takes a minute or two to load, and to make matters worse, I remote desktop to the computer... So, holding the shift button when opening isn't a good option. Also, since the macro triggers FTP downloads and other scripts, ESCing after the macro is not a great option either because I have to "chase" those processes down to stop them.

What I would like to do is have the macro ask a question, such as "Would you like to stop the macro? If no selection is made in 2 minutes, the macro will automatically proceed." If "yes" or "ok" or whatever is not selected, it will proceed, if it is selected, the macro will end and allow me to edit the workbook. In other words, when Windows Scheduler opens it, and I am not there to stop it, it will proceed as planned. If I manually open the file for maintenance, I have the option of stopping the macro before it triggers all of the external scripts.

Any ideas?

Thanks!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

rollis13

Active Member
Joined
Jul 30, 2012
Messages
438
Office Version
  1. 2016
Platform
  1. Windows
I think you could test the "username" which has access to the file and ' If Not "You" then go on Else stop ' so you won't have the need to answer questions.
 
Last edited:

gaj104

Well-known Member
Joined
Nov 9, 2002
Messages
864
If the username method doesn't work, the way to do it is to use a userform. You cant use a standard message box as the code effectively becomes frozen until the user clicks a button.

The steps to carry out would be:
1. Create a userform with button.
2. Add code to the button that closes down the form when clicked
3. Add code to show userform when workbook is opened
4. Use ontime to check 2 minutes after opening if the userform is displayed. If yes then close userform and run main code, if no then the user would have pressed the button to close the form, and therefore don't run main code.
 

DBCox

Board Regular
Joined
Jul 5, 2012
Messages
52
Hey guys. I apologize for not getting back to you sooner. Things have been crazy with Christmas right around the corner!

rollis13, thank you for the suggestion, and that would certainly be the smoothest option. Unfortunately, the computer has only one user setup so that it automatically logs on when the computer starts. At the end of the long macro, the machine restarts (will eventually set it up to shut down, then restart at a certain time). So, I need to keep only a single user setup on the computer.

gaj104, I am close to getting your method to work. I have the userform setup asking "Do you want to stop the macro" with a "Yes" and "No" answer that is working. However, I have not been successful at getting the ontime to either automatically close the window and continue, or automate the selection of "no". Do you have an example code? I apologize for my elementary knowledge of VBA.
 

gaj104

Well-known Member
Joined
Nov 9, 2002
Messages
864
Hi,

Here's an example of such a code. Note, the form must be modeless otherwise you will have the same problem as a msgbox. Also the name of my userform is called userform1.

Code:
Private Sub Workbook_Open()

UserForm1.Show (0) 'Modeless
Application.OnTime Now + TimeValue("00:02:00"), "FormOpenChk"

End Sub

Function FormOpenChk()

'Test if form is still showing
If UserForm1.Visible = True Then

    UserForm1.Hide
    Call YourMacro 'Name of your main macro
    
End If

End Function

Hope it helps
 

Watch MrExcel Video

Forum statistics

Threads
1,132,686
Messages
5,654,754
Members
418,151
Latest member
shukoor

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top