• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1009
  • Last Modified:

Excel VBA Auto_Open Macro

Hi all, I have what I think should be a simple question, but I can't get this to work. I have an excel file that uses the Auto_Open sub so that code is run UNATTENDED whenever the file is opened (scheduled task). However, sometimes I would like to open the file manually and prevent this code from running. Here's my macro:
Sub Auto_Open()
   UserForm1.Show
   MsgBox("Auto_Open completed")
end sub

Open in new window


My form has one button only. Here's the code and properties:
Private Sub CommandButton1_Click()
    UserForm1.Hide
    End
End Sub

Private Sub UserForm_activate()
    Application.Wait Now + TimeValue("0:00:05")
    UserForm1.Hide
End Sub

Open in new window

Properties
So what SHOULD happen is:
1) File opens
2) Autorun macro starts
3) Userform1 is opened
4a) if user does nothing, after 5 seconds userfrom1 closes, and then
      the "Auto_Open completed" msg is displayed
4b) however, if user clicks button, ALL macros should stop; i.e. user
      should NOT see  "Auto_Open completed" msg

What actually happens is:
1) File opens
2) Autorun macro starts
3) Userform1 is opened
4) I cannot click the button, so the "Auto_Open completed" msg is always displayed

 I am doing this so I don't have to constantly toggle my macro security settings. Where am I going wrong?
0
ragnarok89
Asked:
ragnarok89
  • 3
  • 2
1 Solution
 
dlmilleCommented:
I did a solution a while back with something similar.  See the attached.  The key thing is when you click a button on the userform, to set a boolean flag to stop further automation.  When the timer goes to kick off your automation it should check that flag first.

here's the code in the userform:
 
Private Sub CommandButton1_Click()
    stopAutomation = True
    Unload UserForm1
End Sub

Private Sub UserForm_Activate()
    Application.OnTime Now() + TimeValue("00:00:05"), "continueAutomation"
End Sub

Open in new window


Here's the code in ThisWorkbook (alternative, use this from Auto_Open):
Private Sub Workbook_Open()
    Call getItGoing
End Sub

Open in new window


And here's the code in Module1 (note the public variable called stopAutomation as Boolean:
 
Public stopAutomation As Boolean
Public Sub getItGoing()
    Load UserForm1
    UserForm1.Show
End Sub
Public Sub continueAutomation()
    Unload UserForm1
    If stopAutomation Then Exit Sub
    'your normal Automated Open macro code goes here
    MsgBox "You're now running the automated open code...", vbOKOnly, "not really - just a prompt for example"
End Sub

Open in new window


So, when the workbook is open, a userform pops up and you have 5 seconds to hit cancel, or the automation (e.g., the macro to run) will kick off.

See attached.

Enjoy!

Dave

coundownToAutomation-r1.xls
0
 
ragnarok89Author Commented:
dlmille,

I see what you're trying to do... However, when I run it, I get 2 errors. First, I think "Public stopAutomation As Boolean" should be "Dim stopAutomation As Boolean" I made this change and got this:

"Wrong number of arguments or invalid property assignment" this error occurs on the line

Application.Wait Now + TimeValue("0:00:05"), "continueAutomation"

I believe it doesn't like the        , "continueAutomation"
0
 
ragnarok89Author Commented:
ok...

Application.OnTime Now + TimeValue("0:00:05"), "continueAutomation"

works, but the complete macro always runs, even if I click the button on the form...
0
 
ragnarok89Author Commented:
OK...

"Public stopAutomation As Boolean" is in fact correct. I understand that part now
0
 
dlmilleCommented:
continueAutomation (and you can change the name) is just the name of a public macro that you want to run if the user doesn't interrupt the process by clicking Cancel which sets the public variable stopAutomation.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now