I have thoroughly read all the posts on automatically running macros from a
change in cell contents. However, if the cell being tested has been updated
from MS query the macro doesn't run. I have checked this by over writing the
data returned from query and the macro runs. If I get the macro to run from
the cell change that invokes the query, the macro has done it's bit before
the data has been returned!

hi
a commom problem. a query refresh does not fire the worksheet change event
just like a recalulation does not fire the work sheet change event.
physically changing data does fire it. also your query is running in the
background which is why your macro finishes before the refresh is done.
solutions.
use the chick event on a button or icon.

sub refreshqueryandrunmacro()
Sheets("sheet1").range("A1").querytable.refresh backgroundquery:=false
'or you can select the query range if desired....
'sheets("sheet1").select
'Range("A1").select
'selecdtion.querytable.refresh backgroundquery:=false
'yourmacro here
end sub

as a rule, if my macro is going to use the data being imported, i do not
allow a background refresh....just in case.
regards
FSt1

"AndyT" wrote:

I have thoroughly read all the posts on automatically running macros from a
change in cell contents. However, if the cell being tested has been updated
from MS query the macro doesn't run. I have checked this by over writing the
data returned from query and the macro runs. If I get the macro to run from
the cell change that invokes the query, the macro has done it's bit before
the data has been returned!

Hi,

Thanks for that. I was trying to avoid buttons or icons, however I can see
that this will solve my issue. Currently I enter a date send it to query,
automatically on cell change, the query returns stock codes for that date and
the macro should then run a unique filter on the stock codes. I will use
your suggestion but if you do know of a way of avoid the chk event on a
button, it would be greatly appreciated.

regards
Andy

"FSt1" wrote:

hi
a commom problem. a query refresh does not fire the worksheet change event
just like a recalulation does not fire the work sheet change event.
physically changing data does fire it. also your query is running in the
background which is why your macro finishes before the refresh is done.
solutions.
use the chick event on a button or icon.

sub refreshqueryandrunmacro()
Sheets("sheet1").range("A1").querytable.refresh backgroundquery:=false
'or you can select the query range if desired....
'sheets("sheet1").select
'Range("A1").select
'selecdtion.querytable.refresh backgroundquery:=false
'yourmacro here
end sub

as a rule, if my macro is going to use the data being imported, i do not
allow a background refresh....just in case.
regards
FSt1

"AndyT" wrote:

I have thoroughly read all the posts on automatically running macros from a
change in cell contents. However, if the cell being tested has been updated
from MS query the macro doesn't run. I have checked this by over writing the
data returned from query and the macro runs. If I get the macro to run from
the cell change that invokes the query, the macro has done it's bit before
the data has been returned!

hi
main problem is that there is no refresh event and i know of no other event
that would do what you want. you might look into the workbook open event but
i would be hesitate to make other suggestions without a better knowlege of
your situation. the click event is pretty much a "when I want" event and
would probable be your best second choise.

Regards
FSt1

"AndyT" wrote:

Hi,

Thanks for that. I was trying to avoid buttons or icons, however I can see
that this will solve my issue. Currently I enter a date send it to query,
automatically on cell change, the query returns stock codes for that date and
the macro should then run a unique filter on the stock codes. I will use
your suggestion but if you do know of a way of avoid the chk event on a
button, it would be greatly appreciated.

regards
Andy

"FSt1" wrote:

hi
a commom problem. a query refresh does not fire the worksheet change event
just like a recalulation does not fire the work sheet change event.
physically changing data does fire it. also your query is running in the
background which is why your macro finishes before the refresh is done.
solutions.
use the chick event on a button or icon.

sub refreshqueryandrunmacro()
Sheets("sheet1").range("A1").querytable.refresh backgroundquery:=false
'or you can select the query range if desired....
'sheets("sheet1").select
'Range("A1").select
'selecdtion.querytable.refresh backgroundquery:=false
'yourmacro here
end sub

as a rule, if my macro is going to use the data being imported, i do not
allow a background refresh....just in case.
regards
FSt1

"AndyT" wrote:

I have thoroughly read all the posts on automatically running macros from a
change in cell contents. However, if the cell being tested has been updated
from MS query the macro doesn't run. I have checked this by over writing the
data returned from query and the macro runs. If I get the macro to run from
the cell change that invokes the query, the macro has done it's bit before
the data has been returned!

Hi,
Many thanks for your assistance, I will stick with the button then!
Andy

"FSt1" wrote:

hi
main problem is that there is no refresh event and i know of no other event
that would do what you want. you might look into the workbook open event but
i would be hesitate to make other suggestions without a better knowlege of
your situation. the click event is pretty much a "when I want" event and
would probable be your best second choise.

Regards
FSt1

"AndyT" wrote:

Hi,

Thanks for that. I was trying to avoid buttons or icons, however I can see
that this will solve my issue. Currently I enter a date send it to query,
automatically on cell change, the query returns stock codes for that date and
the macro should then run a unique filter on the stock codes. I will use
your suggestion but if you do know of a way of avoid the chk event on a
button, it would be greatly appreciated.

regards
Andy

"FSt1" wrote:

hi
a commom problem. a query refresh does not fire the worksheet change event
just like a recalulation does not fire the work sheet change event.
physically changing data does fire it. also your query is running in the
background which is why your macro finishes before the refresh is done.
solutions.
use the chick event on a button or icon.

sub refreshqueryandrunmacro()
Sheets("sheet1").range("A1").querytable.refresh backgroundquery:=false
'or you can select the query range if desired....
'sheets("sheet1").select
'Range("A1").select
'selecdtion.querytable.refresh backgroundquery:=false
'yourmacro here
end sub

as a rule, if my macro is going to use the data being imported, i do not
allow a background refresh....just in case.
regards
FSt1

"AndyT" wrote:

I have thoroughly read all the posts on automatically running macros from a
change in cell contents. However, if the cell being tested has been updated
from MS query the macro doesn't run. I have checked this by over writing the
data returned from query and the macro runs. If I get the macro to run from
the cell change that invokes the query, the macro has done it's bit before
the data has been returned!