Keep Page Visible In Workbook While Macros Run On Other Pages

[Buns1976]

Hi Everyone,
I have a workbook the contains 4 sheets. 3 of them are hidden. I have a macro that unhides those 3 sheets, does some calculations and then hides them.
You can see all of that as it takes place. Is there a way to make that not visible and keep the the 4th page visible as the macros run? I know I can
"Application.WindowState = xlMinimized" the workbook but I need it to be visible until it closes.

Thanks,
B

 

[Domenic]

Oh, hang on a second. You're talking about waiting for a query to finish refreshing? In that case, have you tried setting BackgroundQuery to False so that it waits until the query is finished before continuing?

 

[Buns1976]

Oh, hang on a second. You're talking about waiting for a query to finish refreshing? In that case, have you tried setting BackgroundQuery to False so that it waits until the query is finished before continuing?

I have not. I didn't know that had anything to do with how the workbook runs macros on open? I thought it refreshed the query prior to pivot tables. There are no pivot tables and only the single 4 cell query. The idea was to pause the macros long enough for the refresh to run which is a split second.

 

[Domenic]

You can manually set your query to automatically refresh when the file is opened. By code it would be...

.RefreshOnFileOpen = True

And you can also manually set your query to wait until refresh has finished before continuing. By code it would be...

.Refresh BackgroundQuery:=False

So there should be no need to include code to refresh your query in your workbook open event handler, if that's what you're doing.

 

[Buns1976]

You can manually set your query to automatically refresh when the file is opened. By code it would be...

.RefreshOnFileOpen = True

And you can also manually set your query to wait until refresh has finished before continuing. By code it would be...

.Refresh BackgroundQuery:=False

So there should be no need to include code to refresh your query in your workbook open event handler, if that's what you're doing.

I don't have any refresh built into the current code. What I am after is for the workbook to do the refresh prior to running the code with occurs on workbook open so if I'm reading this correctly, I should put the RefreshBackground=False as the first line in the open_workbook event correct?

 

[Domenic]

Try it like this . . .

Private Sub Workbook_Open()

    ThisWorkbook.Worksheets("Sheet1").ListObjects("TableName").QueryTable.Refresh BackgroundQuery:=False
    
    'your code here
    '
    '
End Sub

Change the sheet name and table name accordingly.

Does this help?

 

[Domenic]

Also, you should manually set the RefreshOnFileOpen property for your query to False. Otherwise your query will be refreshed twice. Once by code in the workbook open event handler, and then again after your code exits the event handler, if this property is set to True.

 

[Domenic]

I'm logging off for this evening. If there are any issues, I'll address them as soon as I get a chance.

Cheers!

 

[Buns1976]

I'm logging off for this evening. If there are any issues, I'll address them as soon as I get a chance.

Cheers!

Thank you very much for your diligence. Doesn't seem to matter what I try I can't get this to refresh consistently. I ended up going with a copy/paste method which is working well. Again thank you!

 

[Domenic]

Maybe there's something I'm missing. In my testing, first I made sure that the property Refresh data when opening file is disabled/unchecked...

Then, in the workbook open event handler, I refreshed the query...

ThisWorkbook.Worksheets("Sheet1").ListObjects("TableName").QueryTable.Refresh BackgroundQuery:=False

And then still in the workbook open event handler, I added a line of code that did a calculation based on the updated/refreshed data. All this seems to work fine. So I don't know why you're still having problems. As I said, maybe there's something I'm missing. If so, please clarify.