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
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
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:
would be enough.Private Sub Worksheet_Change(ByVal Target As Range) if not intersect(target, Range("A1") ) is nothing then call bump end if End Sub
Best Regards,
Kaper
my macro is called bump not my worksheet by the way...
this Private Sub Worksheet_Change shall be in the worksheet code not in general module. (the worksheet which contains A1 cell which changes).
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
any help would be appreciated.........
Try this...
Hopefully this will do...
Paste the above code in Worksheet section, Not in General section.. (In the Dropdown above..)Private Sub Worksheet_Change(ByVal Target As Range) if Target.address=Range("A1").address then call bump end if End Sub
Regards,
Regards,
Vikas Gautam
Excel-buzz.blogspot.com
Excel is not a matter of Experience, its a matter of Application.
Say Thanks, Click * Add Reputation
Hi, burdo77,
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.essentially the number within cell A1 changes every second
@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
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
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...
Hi, Vikas,
maybe because your code should look like
Ciao,If Target.Address = "$A$1" Then
Holger
-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
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:
and in standard module (for instance Module1):Public varA1content Public storedtime As Time Private Sub Workbook_Open() varA1content = Sheets("Sheet2").Range("A1").Value storedtime = Now + TimeValue("00:00:01") Application.OnTime storedtime, "MyBumpCaller" End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnTime storedtime, "MyBumpCaller", , False End Sub
Save workbook, close and open it again to call OnTime storedtime, "MyBumpCaller" in Workbook_Open event handler.Sub MyBumpCaller() If varA1content <> Sheets("Sheet2").Range("A1").Value Then varA1content = Sheets("Sheet2").Range("A1").Value Call Sheet2.bump End If storedtime = Now + TimeValue("00:00:01") Application.OnTime storedtime, "MyBumpCaller" End Sub
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).
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
I have some problems with forum access totay. One more modyfication - public variables
shall be declared in standard module.Public varA1content Public storedtime As Double
As requested - please see attachment with the code inserted.
hi kaper, it works a treat. Your generosity/time is greatly appreciated.
Cheers burdo.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks