Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
macro to automatically update dates | Excel Worksheet Functions | |||
Update Query | New Users to Excel | |||
Update Web Query | Excel Discussion (Misc queries) | |||
automatically update macro | Excel Worksheet Functions | |||
Macro to Create a Chart and Update it Automatically | Charts and Charting in Excel |