Force Recalculation

R

Ron_D

I have a couple of Advance Filter tables where the criteria changes based on
the current row. I would like a macro that will trigger whenever the focus
changes to another cell.

Thanks in Advance,
Ron
 
H

Harald Staff

Hi Ron

Rightclick the sheet tab. Choose "view code". Paste this in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Beep
End Sub

Replace Beep with whatever you want done.

HTH. Best wishes Harald
 
D

David McRitchie

Hi Ron,
For information on recalculation (mentioned in your subject title) see
http://www.mvps.org/dmcritchie/excel/shortx2k.htm#calc

For information on Event macros (the question) see
http://www.mvps.org/dmcritchie/excel/event.htm

Have you turned off automatic calculations?
Is it the content of the worksheet that is not being recalculated
Do your functions reference the cells that get changed?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)'to install -- right-click on the sheettab of the sheet to
' be used in and choose 'view code'. Paste this Worksheet
' event macro into the module.
Application.calculate ' same as F9 you may need more
End Sub

Please do not hide any information pertaining to the question
in the subject title -- chances are most people, including myself,
will not see it, if it is not with the question. As you may have
noticed already from Harald's reply.
 
R

Ron_D

Hi David and Harald,

The application.calculate method and the application.volatile method helped
recalculate my formulas which in turn changed the criteria on the Advance
Filter macro.

Thanks for everything guys,
Ron_D

David McRitchie said:
Hi Ron,
For information on recalculation (mentioned in your subject title) see
http://www.mvps.org/dmcritchie/excel/shortx2k.htm#calc

For information on Event macros (the question) see
http://www.mvps.org/dmcritchie/excel/event.htm

Have you turned off automatic calculations?
Is it the content of the worksheet that is not being recalculated
Do your functions reference the cells that get changed?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)'to install -- right-click on the sheettab of the sheet to
' be used in and choose 'view code'. Paste this Worksheet
' event macro into the module.
Application.calculate ' same as F9 you may need more
End Sub

Please do not hide any information pertaining to the question
in the subject title -- chances are most people, including myself,
will not see it, if it is not with the question. As you may have
noticed already from Harald's reply.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Ron_D said:
I have a couple of Advance Filter tables where the criteria changes based on
the current row. I would like a macro that will trigger whenever the focus
changes to another cell.

Thanks in Advance,
Ron
Click to expand...
Click to expand...
 
Top