change event???

[burdo77]

I have macro within a worksheet called bump ()

what I want to do is automatically trigger the macro when cell A1 changes.

essentially the number within cell A1 changes every second so I am happy to execute the macro every second.

cheers

[Kaper]

What is causing this cell changes - may be it would be wise to use this as a trigger for bump macro?

Otherwise - may be just:

Please Login or Register  to view this content.

would be enough.

[burdo77]

my macro is called bump not my worksheet by the way...

[Kaper]

this Private Sub Worksheet_Change shall be in the worksheet code not in general module. (the worksheet which contains A1 cell which changes).

[burdo77]

sorry that doesn't work,,

I have a countdown timer in cell A1 I basically want the macro to trigger every one second off that....

if I alter the cell such as deleting the contents it runs the macro, but not when the countdown timer changes

[burdo77]

any help would be appreciated.........

[Vikas_Gautam]

Try this...
Hopefully this will do...

Please Login or Register  to view this content.

Paste the above code in Worksheet section, Not in General section.. (In the Dropdown above..)
Regards,

[HaHoBe]

Hi, burdo77,

essentially the number within cell A1 changes every second

How is that done? Via Query? By VBA? What does your macro bump look like? What events are behind the sheet? Maybe post a sample workbook as attachemnt.

@Vikas:
I like Kaperīs approach better (looks more like the way I use to program) while both codes should work the same way.

Ciao,
Holger

[Vikas_Gautam]

Thanks for the opinion.. Holger..
But when both will perform the same function how can one be better of other..?
I am just asking for knowledge purpose..
I have no problem in using Intersect approach...

[HaHoBe]

Hi, Vikas,

maybe because your code should look like

Please Login or Register  to view this content.

Ciao,
Holger

[burdo77]

-I have attached my sheet.
-The countdown timer is produced via an API server which places it into cell A1 Sheet2. no VBA required for the countdown timer!!!!
-If you make a simple macro button you will see what my macro does,
-however I want that macro to work every second when the countdown timer ticks.
-Bump macro is in Sheet2.

cheers burdo

[Kaper]

1) intersect seems to be beter suited in general case because target coukd be column A or A1:C5 etc.

2) we still know almost nothing about what really changes. You can try force excel to check periodically if something changed.
For instance in ThisWorkbook module:

Please Login or Register  to view this content.

and in standard module (for instance Module1):

Please Login or Register  to view this content.

Save workbook, close and open it again to call OnTime storedtime, "MyBumpCaller" in Workbook_Open event handler.
Then every second it will automatically check if current content of Sheet2!A1 is different than one stored in previous second. And if it is, it will call bump procedure in Sheet2 module (by the way - is it there for any purpose? Because typical location would be in standard module).

[burdo77]

I tried both code but still the same result... it didn't call my macro..

I may of done it wrong so can u plz paste the code in the spreadsheet.. im a newbie at this so plz be patient

[Kaper]

I have some problems with forum access totay. One more modyfication - public variables

Please Login or Register  to view this content.

shall be declared in standard module.

As requested - please see attachment with the code inserted.

[burdo77]

hi kaper, it works a treat. Your generosity/time is greatly appreciated.

Cheers burdo.