Run a macro on change of a value

Worksheet_Change event won't work for this type of application.  You'll have to trap it with some other event, like calculate, or sheet selection change, or with a timer that looks every so many seconds.

Before I provide further assistance, think on this and decide what event you'd LIKe to catch the change in the LTPS

Read this thread as there's another E-Eer that does stuff like this and you might find these solutions useful:

Here's one that makes an alert when a cell value changes, so the traps are similar to what you're trying to do - in this case, the timer is started and every so much time, the values are tested and if changed, BING - you have what you need...

http:/Q_27417925.html

Dave

Here's how to do it with the timer:

 

Public runWhen As Double

Sub startTimer()
Dim refreshTime As Double
Dim waitMins As Long

    Call myMacro
    waitMins = 5
    refreshTime = 15 ' after testing at 15 seconds, then change to -> waitMins * 60 'time in seconds
    runWhen = Now() + refreshTime / 86400 '# seconds in 24 hours
    Application.OnTime earliesttime:=runWhen, procedure:="StartTimer", schedule:=True

End Sub
Sub StopTimer()
    On Error Resume Next
    Application.OnTime earliesttime:=runWhen, procedure:="StartTimer", schedule:=False
    
End Sub
Sub myMacro()
'check you cell values, here and do what you want to do if there was a change
end sub

Select allOpen in new window

Be sure on workbook_close, you run the stoptimer, just in case it hadn't been stopped.

Let me know if you need more assistance.

Cheers,

Dave

HI Dave,

Thanks for your response.

As a matter of fact, I have already tried these options. But the nature of the job is such that I can't use a time based option. I must run the macro only when a change in LTP happens. Because if there is a change in the LTP during that time gap, I will miss it. And I can't have the time gap too close as it will choke the system.

I also can't use the calculate option as it will not give me the 'Target' and also the 'selection_change' is not going to work for me.

Please give me something else....

Thanks
Joydip

Hi, Joydip.

Please see attached. Couple of points...
(1) For testing purposes, I have put a formula in A1 to force a WorkSheet_Change event.
(2) Most of the WorkSheet_Change() code is to handle possible error values in the LTP cells. If the "Bloomberg" formulas in those cells can never return an error then the code can be simplified.
(3) Events are temporarily disabled while WorkSheet_Change() is running. If you have other active Event macros, please let me know.

Option Explicit

Private Sub Worksheet_Calculate()
Dim xCell As Range
Dim xValue As Variant
Dim xValue_Old As Variant
Dim xChange As Boolean
Dim xNow As String

Application.EnableEvents = False

    xNow = Format(Now(), "hh:mm:ss")
    
    For Each xCell In Sheets("Sheet1").Range("B3:F3")
        xValue = xCell.Value
        If IsError(xValue) Then xValue = "*Error*"
        xValue_Old = Sheets("Old_Sheet1").Range(xCell.Address)
        If IsError(xValue_Old) Then xValue_Old = "*Error*"
        If xValue <> xValue_Old Then
            xCell.Offset(-1, 0).Value = xNow
            xChange = True
        End If
    Next
    
    If xChange Then Call Refresh_Old_Condition_Values

Application.EnableEvents = True

End Sub

Select allOpen in new window

Option Explicit

Sub Auto_Open()

Application.EnableEvents = False

    Call Refresh_Old_Condition_Values

Application.EnableEvents = True

End Sub

Sub Refresh_Old_Condition_Values()

Application.ScreenUpdating = False

    Worksheets("Sheet1").Range("B2:F3").Copy Destination:=Sheets("Old_Sheet1").Range("B2")
    
Application.ScreenUpdating = True

End Sub

Select allOpen in new window

Regards,
Brian.
ltp-change-V2.xlsm

Joydip,

Apologies, my previous post refers to "WorkSheet_Change" when, of course, it should be "WorkSheet_Calculate". The code is correct.

Regards,
Brian.

If the bloomberg update does not affect a worksheet_change and timer doesn't work, you might be "out of luck".

Brian - I might stand corrected and blame it on short-term memory lapses, but I don't believe worksheet_calculate will be triggering on the change either, if worksheet_change doesn't.  IMHO - your best chance might be to put the =NOW() function in the worksheet and reference that cell from the Bloomberg cells in a non-interfering way.  MAYBE it will be updated when Bloomberg updates as a result, THEN calculate might work as the trigger.

Don't believe its going to work all on its own.

Dave

dlmille,

I know my code worked cpatte7372, but of course Bloomberg may us a different method.

Joydip,

How does the update work - as a formula in the cell or some other way? Did the software come from Bloomberg or somewhere else?

Thanks,
Brian.

Brian -  knew those of us whod helped Carlton might be of help here

Keep the now reference in your back pocket as might be of use

Cheers

Thanks, Dave. If the Sum() in A1 doesn't trigger a calculate then we may be dead. I know that Now() is volatile, but if Excel doesn't realise that the LTPS cells have changed (hard to believe) then it won't even be thinking about a Recalculate.

I was operating of the original OP post that worksheet_change wasn't impacted.  Perhaps sum() is just as effective as now() - if memory serves now was used in all examples I read to affect a change.  At any rate, we'll see soon, when the OP responds to your post.

Dave

Dave, things usually get better with experience, but I'm not sure that works for memory. I suspect that my "experienced" memory is somewhat, eh, older than yours and so I'm about to re-learn something!

Hi Guys,

Really appreciate your time and effort regarding this issue.

My apologies for not posting sooner.

I must provide you the following information :

a) The Worksheet_Calculate option does work in that sheet! Whenever the LTP is changed the sheet seems to get calculated.
b) This option of getting live data comes with Bloomberg software itself. It is not a Third party software.
c) To get the LTP or any other market related data, we must enter a formula.
d) The result of any formula with reference with the cells getting changed by Bloomberg formulas do change, but those can not be captured by the worksheet_change method. For example, any cell containing the formula of the sum of all the LTP do change when any of the LTPs change, but it does not trigger the worksheet_change.

Looking forward for you guys to give me way out on this...

Thanks and regards,
Joydip

The problem with using the 'calculate' option is that I can't pin point the exact contract for which the LTP has changed.

Joydip,

All that's OK.

"I can't pin point the exact contract"
 - The macro indirectly detects that some change has been made to one or more LTP's by the formula in A1.
 - Because it has stored the previous values of the LTP, it is then able to compare the old and new values to detect which  LTP has changed.

Give it a go!

Finally, how many LTP cells do you have in your live file?

Regards,
Brian.

Hi Brian,

I have about 30 LTP cells in my live file.

Can you please resend me the file with the code in it so that I can find out in the Bloomberg system whether it is working or not?

Thanks,
Joydip

Joydip,

Resend? Is there a problem with http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27419963.html#a37043945?

Regards,
Brian.

Hi Brian,

Never mind....I did not see that you had already updated the file :)

Let me check that in the bloomberg system and then I will get back you you.

Thanks a lot,
Joydip

Hi Brian,

Sub Refresh_Old_Condition_Values()

Application.ScreenUpdating = False

    Worksheets("Sheet1").Range("B2:F3").Copy Destination:=Sheets("Old_Sheet1").Range("B2")
    
Application.ScreenUpdating = True

End Sub 

Select allOpen in new window

Need to paste value in order to compare. As there are formulas in the LTP row which depend on reference of the contract names.

Thanks,
Joydip

Joydip,

Please see attached. Only code change is...

Sub Refresh_Old_Condition_Values()

Application.ScreenUpdating = False

    Worksheets("Sheet1").Range("B2:F3").Copy
    
    Sheets("Old_Sheet1").Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    
Application.ScreenUpdating = True

End Sub

Select allOpen in new window

Regards,
Brian.

Hi Brian,

Thanks, it is working fine.

I would love to have a solution with worksheet_change somehow though...It would make my job alot easier.

Appreciate your help greatly.

Regards,
Joydip

Joydip,

Thanks kindly!

"It would make my job alot easier"
It would also be more efficient - my code runs more often than necessary and then has to do more work to identify the specific cell(s). Unfortunately, a formula result changing doesn't trigger the Worksheet_Change event.

I don't suppose there are any set-up parameters for the Bloomberg s/w that can be tweaked to update the spreadsheet differently?

I've never seen the Bloomberg s/w in action - how often do the updates arrive? Do they come individually or in blocks (e.g. every second the changes for the previous second all arrive together? Does the active cell change?

Regards,
Brian.

Hi Brian,

I am not sure whether it is possible to tweak that software or not. I will try to give it a try though.

The updates appear as soon as there is any change in the market. It is real time. They don't come in blocks. The active cell never change. Only the value in which we have called the Bloomber formala, changes.

Do you think anyone here might have an idea about how we can solve this issue?

Regards,
Joydip

Joydip,

Thanks for the answers. Unfortunately I don't see anything that can be used to generate a Change event - we're hard up against the fact that a formula's result changing simply doesn't fire a Change event.

You should try a new question with a title something like "How can I detect Bloomberg updates as Change events?". Explain that you already have a method for picking up the changes but that you're looking for an easier and/or more efficient solution. Include a link to this question. Also, add a few more zones - I googled  "site:experts-exchange bloomberg" and saw quite a few Access-related questions. (Even if Access isn't a runner for you, you may pick up some Bloomberg experts.)

Having been unable, so far, to help with your follow up, is there anything I can do to make the current solution easier for you?

Regards,
Brian.

Hi Brian,

You have helped me a lot!

I will let you know if there is anything else I need to know regarding this.

Thanks and regards,
Joydip