[dutty]

I have a user form set to load and show upon file open that has two fields
for user parameter entry--Customer, and Start Date. Button macro pastes this
information into cells in Sheet 1 and Sheet 2, which contain queries to an
external database. Queries are set to automatically refresh when the cells
change. Sheet 3 contains 2 pivot tables, which after the pasting from the
user form is done, are coded to refresh (then user form hides). The problem
is, the pivot tables refresh before the queries refresh, because the user has
to select the "Enable Automatic Refresh" button after the user form hides.

Can I use VB code to enable the automatic refresh when the sheet opens, and
then time the refresh of the pivot tables to follow the queries' refresh?

[sebastienm]

Hi,
I think it is because the "background query refresh" is enabled ?
To prevent this to happen:
- if refreshing the queries through code, refresh with BackgroundQuery=False
my_queryTable.Refresh False
- if within excel, in the Data Range Properties, uncheck the "Enable
Background Refresh"

Rgeards,
Sebastien

"dutty" wrote:

I have a user form set to load and show upon file open that has two fields
for user parameter entry--Customer, and Start Date. Button macro pastes this
information into cells in Sheet 1 and Sheet 2, which contain queries to an
external database. Queries are set to automatically refresh when the cells
change. Sheet 3 contains 2 pivot tables, which after the pasting from the
user form is done, are coded to refresh (then user form hides). The problem
is, the pivot tables refresh before the queries refresh, because the user has
to select the "Enable Automatic Refresh" button after the user form hides.

Can I use VB code to enable the automatic refresh when the sheet opens, and
then time the refresh of the pivot tables to follow the queries' refresh?

[dutty]

Queries aren't refreshing through code, they are doing so automatically when
the data is pasted from the user form via the "Refresh automatically when
cell value changes" function under Parameters.

I unchecked the "Enable Background Refresh" option and tried to run the
procedure again with the same results--the pivot tables refresh prior to the
queries refresh operation completing??

dutty

"sebastienm" wrote:

Hi,
I think it is because the "background query refresh" is enabled ?
To prevent this to happen:
- if refreshing the queries through code, refresh with BackgroundQuery=False
my_queryTable.Refresh False
- if within excel, in the Data Range Properties, uncheck the "Enable
Background Refresh"

Rgeards,
Sebastien

"dutty" wrote:

I have a user form set to load and show upon file open that has two fields
for user parameter entry--Customer, and Start Date. Button macro pastes this
information into cells in Sheet 1 and Sheet 2, which contain queries to an
external database. Queries are set to automatically refresh when the cells
change. Sheet 3 contains 2 pivot tables, which after the pasting from the
user form is done, are coded to refresh (then user form hides). The problem
is, the pivot tables refresh before the queries refresh, because the user has
to select the "Enable Automatic Refresh" button after the user form hides.

Can I use VB code to enable the automatic refresh when the sheet opens, and
then time the refresh of the pivot tables to follow the queries' refresh?