Macro Ask User Input, Proceed if Ignored

[DBCox]

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!

 

[rollis13]

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.

 

[gaj104]

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]

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]

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.

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