Block Excel while TM1 refresh?

[andreask]

Hi,

Is there any possibility to avoid user interaction (mouse / keyboard) while tm1 is refreshing the worksheet?

I can't find any event or chance how to detect when TM1 is starting or finishing the process. The block code itself in VBA should be no problem...

Background is the following: We have a few users which complain about some excel crashes. I guess that they click or do stupid things during the refresh...

Any ideas?

[Christopher Kernahan]

Hi Andreask,

Rather than automatically reaching for a coding hammer to solve this issue, why don't you observe what the users are doing, or ask them to describe what happens? It might save you some time.

In terms of preventing clicking etc, you could force users to execute a sheet recalc under a VBA Macro button and prevent screen updating in that function. I've seen someone homebrew a progress bar by using VBA to update a user form and refreshing blocks of DBRWs to provide a slightly more meaningful timeframe, but this is only really necessary for very large sheets.

[andreask]

I asked them...

"Excel chrashes every 10 minutes!!!"

"I don't do anything and it crashes!"

and so on I completely tested the hole enviromnent without any problems and when I look over the shoulder it is no problem. But...they still complain about problems. I told them to wait until the report is build, but it takes some time (citrix env.).

I dit something already with a progress bar which works fine (without crashes). But I cannot assure that the report is not refreshed when the user opens it - depends on the calculation mode which is always different I guess..!?

[Martin Ryan]

There are a couple of ideas in this thread: http://www.tm1forum.com/viewtopic.php?f=3&t=4759

depends on the calculation mode which is always different I guess..!?

As a general rule users shouldn't have auto calculate on when using TM1, it will slow them down, and any one else who is using TM1. They should only recalculate when they need to refresh the latest numbers from TM1.

[andreask]

There are a couple of ideas in this thread: http://www.tm1forum.com/viewtopic.php?f=3&t=4759

Thanks - but that I did that already. It's useful when I control the refresh itself by VBA. But what I wrote about was the normal TM1 refresh, ie opening an active form.

depends on the calculation mode which is always different I guess..!?

As a general rule users shouldn't have auto calculate on when using TM1, it will slow them down, and any one else who is using TM1. They should only recalculate when they need to refresh the latest numbers from TM1.

Hmm ok. I noticed a strange behaviour in the citrix env.: I switch to manual. I reopen the excel, still manual. At some point it switches back to automatic, dont know why. Never changed the value, don't have any VBA code which modifies the value etc. Seems not possible to set the value per default to manual (and of course it is useful in the automatic mode while you're working eith excel without tm1 - in my opinion the problem is tm1 rather than excel mode).