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!
 

rollis13

Board Regular
Joined
Jul 30, 2012
Messages
247
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
 

Forum statistics

Threads
1,082,145
Messages
5,363,389
Members
400,735
Latest member
amit Misra

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top