From: Martin Parker on
Hi

I am pulling in currency exchange rate external data from a web site... no
problems and storing the information on a worksheet.

I have a userform with txtboxes that are linked to each of the cells. When
the user form loads, the txtbox shows the currency exchange rate data
imported in from the web site...agian no problems.

I want to have a cmdbutton on the userform to update the data, i'm using:-

ActiveWorkbook.RefreshAll

but my question is the actual refresh will no take place until the userform
is closed. How can i make the refresh data run whilst the userform is
showing? Is this possible. I would like to click the command button to update
the data within the worksheet and show the updated rates within each of the
linked txtboxes on the userform without having to close the form down and
re-open.

Hope you can help.

TIA
From: Martin Parker on
To clarify a little further, I am using the following code to refresh data
from a cmdbutton on a userform:-

ActiveWorkbook.Connections("ConnectionCopper").Refresh

However, the data is not refreshed until the userform is close. I would like
to code to be updated whilst the userform is loaded. Is this possible?

Cheers


"Martin Parker" wrote:

> Hi
>
> I am pulling in currency exchange rate external data from a web site... no
> problems and storing the information on a worksheet.
>
> I have a userform with txtboxes that are linked to each of the cells. When
> the user form loads, the txtbox shows the currency exchange rate data
> imported in from the web site...agian no problems.
>
> I want to have a cmdbutton on the userform to update the data, i'm using:-
>
> ActiveWorkbook.RefreshAll
>
> but my question is the actual refresh will no take place until the userform
> is closed. How can i make the refresh data run whilst the userform is
> showing? Is this possible. I would like to click the command button to update
> the data within the worksheet and show the updated rates within each of the
> linked txtboxes on the userform without having to close the form down and
> re-open.
>
> Hope you can help.
>
> TIA
From: om on
Replace "Activeworkbook.Refreshall" with "Selection.QueryTable.Refresh
BackgroundQuery:=False"

This works exactly the way you wanted.

Cheers
Om :)

"Martin Parker" wrote:

> To clarify a little further, I am using the following code to refresh data
> from a cmdbutton on a userform:-
>
> ActiveWorkbook.Connections("ConnectionCopper").Refresh
>
> However, the data is not refreshed until the userform is close. I would like
> to code to be updated whilst the userform is loaded. Is this possible?
>
> Cheers
>
>
> "Martin Parker" wrote:
>
> > Hi
> >
> > I am pulling in currency exchange rate external data from a web site... no
> > problems and storing the information on a worksheet.
> >
> > I have a userform with txtboxes that are linked to each of the cells. When
> > the user form loads, the txtbox shows the currency exchange rate data
> > imported in from the web site...agian no problems.
> >
> > I want to have a cmdbutton on the userform to update the data, i'm using:-
> >
> > ActiveWorkbook.RefreshAll
> >
> > but my question is the actual refresh will no take place until the userform
> > is closed. How can i make the refresh data run whilst the userform is
> > showing? Is this possible. I would like to click the command button to update
> > the data within the worksheet and show the updated rates within each of the
> > linked txtboxes on the userform without having to close the form down and
> > re-open.
> >
> > Hope you can help.
> >
> > TIA
From: om on
Make sure you select at least one cell within your imported data before you
run the macro.

"Martin Parker" wrote:

> To clarify a little further, I am using the following code to refresh data
> from a cmdbutton on a userform:-
>
> ActiveWorkbook.Connections("ConnectionCopper").Refresh
>
> However, the data is not refreshed until the userform is close. I would like
> to code to be updated whilst the userform is loaded. Is this possible?
>
> Cheers
>
>
> "Martin Parker" wrote:
>
> > Hi
> >
> > I am pulling in currency exchange rate external data from a web site... no
> > problems and storing the information on a worksheet.
> >
> > I have a userform with txtboxes that are linked to each of the cells. When
> > the user form loads, the txtbox shows the currency exchange rate data
> > imported in from the web site...agian no problems.
> >
> > I want to have a cmdbutton on the userform to update the data, i'm using:-
> >
> > ActiveWorkbook.RefreshAll
> >
> > but my question is the actual refresh will no take place until the userform
> > is closed. How can i make the refresh data run whilst the userform is
> > showing? Is this possible. I would like to click the command button to update
> > the data within the worksheet and show the updated rates within each of the
> > linked txtboxes on the userform without having to close the form down and
> > re-open.
> >
> > Hope you can help.
> >
> > TIA