Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Suppressing pop up message when running macro

0

I have a macro to open up a bunch of files, copy data into another workbook and close the files again. Everything works fine except for each file when it's closing the message asking if I want to save it keep coming up even though within the macro I have set the displace alert to False. Not sure what I'm doing wrong? Thanks.

Sub Open_Files()    
    Dim MyFile As String
    MyPath = Range("_location").Value
    MyFile = Dir(MyPath)

    Set wb1 = ActiveWorkbook
    Application.DisplayAlerts = False

    Do While MyFile <> ""

        If MyFile Like "*.xlsx" Then

            Workbooks.Open MyPath & MyFile

            Sheets("Cost Forecast").Rows("3:250").Copy
            wb1.Sheets("Consolidated Cost Forecast").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues


            Application.CutCopyMode = False
            ActiveWorkbook.Close savechanges:=False
        End If

        MyFile = Dir

    Loop

    Application.DisplayAlerts = True
    MsgBox "Forecasts consolidated.", vbInformation

End Sub
excelmacros-vba
posted by: Cathy (rep: 53) on Fri Dec 7, 2018 at 3:52 pm
Answer
Discuss

Answers

0
Selected Answer

I suspect that the Workbooks.Open command resets the DisplayAlerts property. If this is so, moving Application.DisplayAlerts = False to the line before the Close command should make a difference.

If that doesn't work (any anyway, anyway) I would properly assign the ActiveWorkbook to a variable. To be clear, you have ThisWorkbook (containing the code), the ActiveWorkbook (containing the Forecast consolidation) and the temporarily opened workbook, all becoming the ActiveWorkbook at one time or another in quick succession while your code runs. If that might confuse me it might also confuse Excel.

posted by: Variatus (rep: 4889) on Fri Dec 7, 2018 at 7:20 pm
Discuss

Discussion

Given what you pointed out about the ActiveWorkbook I'm surprised this whole thing didn't crash and burn on me. This is what happens when a Novice tries to patch multiple codes together into one. Frankly I was surprised how well it worked with the only issue being the pop-up message.

I've added "Application.EnableEvents = False" into the macro which seems to have done the trick. 

Thanks for your time in answering.
Cathy (rep: 53) Dec 10, '18 at 9:42 am
Make sure to enable the events again at the end of the macro so everything works as expected in Excel.
don (rep: 1989) Dec 10, '18 at 11:07 am
Yes indeed I have "re-enable" it. Would not be a good thing at all if I didn't.

Thanks for the reminder all the same.

Can I just say, don and Variatus, you guys are awesome for being so helpful on this website.
Cathy (rep: 53) Dec 10, '18 at 3:26 pm
You're welcome, and you can definitely say this :)) Thanks :)
don (rep: 1989) Dec 10, '18 at 4:31 pm
Hello Cathy,
If the answer was helpful to you please make sure to mark it as "Selected". This serves as a guide to others and awards points to the one who wrote the answer. Thank you.
Variatus (rep: 4889) Dec 10, '18 at 7:23 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login