×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VBA - Execute Macro on Workbook Open

VBA - Execute Macro on Workbook Open

VBA - Execute Macro on Workbook Open

(OP)
I have 3 xlsm workbooks with a bunch of macros inside of them.. When each one is opened, the user is presented with a pop up that asks if they want to execute the Call_All macro which would run them all. If the user clicks yes, then it executes, and if no then nothing happens.

The reason I have done it this way, is because I am trying to automate the admin work as far as possible and make the process as "dummy proof" as I can for the users. So if I don't put the pop up to ask the user what they want to do and I simply call the Call_All macro in the "this workbook" file, then whenever the file is opened - even if it is to modify something (such as code), the entire macro will execute.

Now, the issue I am having is this:

I run a series of scripts and the very last script opens up the first workbook with the "Yes/No" pop up. When the user clicks on yes for this workbook, the macro's are executed perfectly. The very last macro in each workbook, opens up the next xlsm file, upon which the user is asked again if they want to execute the macros in the new workbook. However, this time it does not execute and absolutely nothing happens. The very same thing happens again when the next xlsm file is opened.

Note: If I open each xlsm file individually and click yes, then they work fine. It is only when I open the file from the previous workbook.

Here is the code to call the Call_All macro (This is in the "ThisWorkbook" File):

CODE --> vba

Private Sub Workbook_Open()

    Dim varResponse As Variant

    varResponse = MsgBox("Run Macro?", vbYesNo, "Selection")
    If varResponse <> vbYes Then

        Exit Sub

    Else

        Call_All

    End If
End Sub 

The code is exactly the same in all 3 workbooks.

There isn't any line with Application.EnableEvents = False but there is Application.ScreenUpdating = False & Application.DisplayAlerts = False

I put Application.ScreenUpdating = True & Application.DisplayAlerts = True at the end of the macro before the next workbook is opened and it still has not changed anything.

I have even put Application.EnableEvents = True in the "ThisWorkbook" file of the workbook that is opened but nothing happened.

RE: VBA - Execute Macro on Workbook Open

Hi,

Quote:

I run a series of scripts and the very last script opens up the first workbook with the "Yes/No" pop up.

In the procedure that opens the first workbook is where you ought to open all the workbooks, rather than in a chain.

I occasionally had circunstances where a periodic update was needed in a workbook, and I used a lockout method where the Workbook_Open Event always ran but checked a flag that was set each time the update procedure ran, storing some value, like Date/Time in a VeryHidden sheet in the workbook. So no Yes/No was ever required, but the update occurred any time the workbook was opened and needed to be updated, based on the stored value flag.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close