Macro only working when I step through

[NWalsh3000]

Ok, so I have made my self pretty decent at figuring out how to do things in excel but I have no formal training so sometimes I miss things that are right in front of me. I have written this macro to refresh my whole workbook and then close the workbook. I have some hidden tabs as well as lock work sheets so there is a little extra vba in it. My problem is the macro fails when i attach it to a shape (button) or just run it. However it works perfectly when I use the step through function. Does anybody see my mistake? Here are the details THANK YOU!

OK so after I run I get a popup box that says "This will cancel a pending data refresh, Continue?" If I click "OK" It finishes the macro but cancels the refresh and close I need.

However like I said, If i step through the macro it works like a charm.

Here is my Macro:

Sub Refresh_Close()
'
' Refresh_Close Macro
'


'Screen Off

Application.ScreenUpdating = False

'Show Tabs

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws

'Unprotect
Call UnProtect_All

'Refresh All & Pause 10 Secs

ActiveWorkbook.RefreshAll
'Application.Wait (Now + TimeValue("00:00:10"))

'Protect
Call Protect_All

'Hide Tabs (ALL EXCEPT Admin)

Sheets(Array("Inventory (Beer)", "Inventory (Liquor)", "Inventory (Wine)", "Order (Beer)", _
"Order (Liquor)", "Order (Wine)", "Mt Beer Pricing", "Mt Liq Pricing", "New Product" _
, "DD & INFO", "Totals", "Invoices", "Spill & Transfer", "Admin", "Cost (Beer)", "Cost (Liquor)", "Cost (Wine)", "Mt Wine Pricing")).Select
ActiveWindow.SelectedSheets.Visible = False

'Save Workbook * Pause 5 Seconds
ActiveWorkbook.Save
'Application.Wait (Now + TimeValue("00:00:05"))

'Screen On
Application.ScreenUpdating = True

'Close Workbook
Application.Quit


'
End Sub

Thanks in advance for all your help!
Norm

 

[NWalsh3000]

I have the two Application.wait disabled in the example above. I get the same result with or without those lines. I put those in as a recommendation from a friend but no luck. Thanks!

 

[tonyyy]

Just a thought... comment out the line:

Application.ScreenUpdating = False

and then try running the macro. Could be it's interfering with the .Visible property or your Unprotect_All script.

Cheers,

tonyyy

 

[NWalsh3000]

Thank you for your reply! I just figured it out. I had background refresh turned on :/ Thanks Again!!!

 

[tonyyy]

Good for you! Glad you worked it out!