If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Can a macro be automatically run after an update from MS query?
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! |
#2
|
|||
|
|||
Can a macro be automatically run after an update from MS query?
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! |
#3
|
|||
|
|||
Can a macro be automatically run after an update from MS query
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! |
#4
|
|||
|
|||
Can a macro be automatically run after an update from MS query
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! |
#5
|
|||
|
|||
Can a macro be automatically run after an update from MS query
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! |
Thread Tools | |
Display Modes | |
|
|