VBA Worksheet change not taking into account new TM1 entry

[Catherine]

Hello,

I have an Excel slice, in which I want to intercept entries to launch a TI process. Each time a user entries a new data in a specific range of cells (containing DBRW formulas), I want a TI process to run to update some related data in an other cube.

I use the VBA event Worksheet_Change, which takes into account the new entry. But the data that has been changed is not in the cube until the sub ends. That means that my TI process runs with the old value.
It seems that the link between the cell and the cube (ie DBRW formula) is done after the end of the actions asked in the sub Workksheet_change.

I hope my question is clear. Don't hesitate to ask if you need some more explanation.
Have you ever met this problem? Do you have some ideas?

Thanks in advance
Catherine

[declanr]

If you have a look around the forum there are plenty of other posts relating to the order in which DBRWs are executed, more so around the reason that you can't use 1 DBRW in the reference of another but I believe the same principles apply to what you are experiencing.

[Catherine]

If you have a look around the forum there are plenty of other posts relating to the order in which DBRWs are executed, more so around the reason that you can't use 1 DBRW in the reference of another but I believe the same principles apply to what you are experiencing.

Thanks for your answers.
I know some problematics about order of DBRWs. But I didn't find anything related to VBA, and more especially to worksheet change event.
Does anybody have an idea how to solve/to workaround my problem?

[declanr]

Can't you just stick an action button in for the user to initiate after changing values?

I understand that the ability to have the process run automatically via VB would be a benefit but I tend to avoid using VB with TM1 where possible simply due to the fact that I have experienced too many times the hassles of upgrading tm1 just to find that there is some special VB code that no longer works or equally vb code that no longer works after upgrading excel.

Its all down to personal preference of course but I try to fall more on the side of "future-proofing" models where possible as opposed to making them look flashy in the intermediate time-frame.

[asutcliffe]

I tend to side with Declan and avoid VBA as much as possible. It's just one more thing that can break.

What happens if you explicitly call TM1Recalc before triggering the TI process? If the only data that should change is the cell pointed to the DBRW, I don't see that this should matter but may be worth a try.

It also sounds like you want to have data elsewhere change dynamically in response to the change the user is making. I assume you've considered using a rule to achieve this rather than TI in the first place or am I missing the point?

[lotsaram]

I think the order of when TM1 takes over the Worksheet_Change event versus when you can also try and capture the event just is what it is. (I have been very frustrated in the past by the fact that the FollowHyperlink event is actually the AfterFollowHyperlink event and there is no BeforeFollowHyperlink, but hey you learn to live with it.)

In this case though do you REALLY need to automate to that extent as opposed to trusting the users to click a button to process further updates? Or why can't you have a process at the conclusion that runs after all such updates that might be triggered when the user exits the sheet or even just entirely in the background?

[Catherine]

What happens if you explicitly call TM1Recalc before triggering the TI process? If the only data that should change is the cell pointed to the DBRW, I don't see that this should matter but may be worth a try.

Thanks for the idea of explicitly calling TM1Recalc but it does not work.

It also sounds like you want to have data elsewhere change dynamically in response to the change the user is making. I assume you've considered using a rule to achieve this rather than TI in the first place or am I missing the point?

You're not missing the point. I have considered using a rule but views are too long to display, considering the rule complexity, and our big volume of data.

Thanks all for your suggestions. I will use an action button, and run the process after all entries in the sheet.