Excel Mouse Cursor with TM1RECALC1

[dubs]

Hi All,

Bit of a funny one this but here goes:

Is there any way to stop Excel displaying the normal mouse icon (ie pointer) whilst it is running this:

Code: Select all

Application.Run "TM1RECALC1"

essentially due to some performance issues the mouse pointer is showing whilst a sheet is calculating which my end users think means the sheet is ready for use, then they start clicking on the sheet which messes up the VBA running, I know i know I should tell my end users to be patient but it's not working - I've managed to ensure that whilst my VBA is running that they cant do anything with the cursor by doing this:

Code: Select all

Application.Cursor = xlDefault
Application.ScreenUpdating = True

but as soon as the call to the .XLA happens the cursor defaults to the pointer and I cant stop the end user from clicking whilst its mid calculation.

any ideas?

[jim wood]

The other 2 copies of this post have been removed.

The Admin Team.

[Martin Ryan]

I take it this for your hand crafted vba, not just a shift+f9 calc?

What I used to do (I try to steer clear of VBA heavy workbooks now) is create a worksheet that simply says "TM1 is updating, please wait...". Then something like (off the top of my head, you'll have to get the syntax)

Code: Select all

thisSheet=activesheet.name
sheets("calcMessage").activate
application.screenupdating = false
sheets(thisSHeet).activate
Application.Run "TM1RECALC1"
application.screenupdating = true

[dubs]

The other 2 copies of this post have been removed.

The Admin Team.

sorry about that chrome wasn't refreshing the page properly so I didn't the first two had posted.

[dubs]

I take it this for your hand crafted vba, not just a shift+f9 calc?

What I used to do (I try to steer clear of VBA heavy workbooks now) is create a worksheet that simply says "TM1 is updating, please wait...". Then something like (off the top of my head, you'll have to get the syntax)

Code: Select all

thisSheet=activesheet.name
sheets("calcMessage").activate
application.screenupdating = false
sheets(thisSHeet).activate
Application.Run "TM1RECALC1"
application.screenupdating = true

yeah I try to but these things can get out of control once users get their hands on them

yeah its essentially a refresh after a number of things have been checked so asking them to do a shift+F9 at a certain point of the work flow is even harder to manage, plus the end users actually want it to be automatic so its down to doing a bit of vba (which i never really enjoy )

so what is happening is that during the recalc the excel cursor gets set to xlNorthwestArrow - not sure why but it is prompting the users to think it isn't calculating..

an other ideas? Martins solution will probably do it but the end users wont like it

[Wim Gielis]

Hi there,

or maybe:

Code: Select all

Sub WiGi_suggestion()

    Application.WindowState = xlMinimized

    'your recalc stuff

    Application.WindowState = xlMaximized

End Sub

Wim

[dubs]

hmmm this is not working, problem is when the TI button is working, can intercept the calc event but it is still defaulting the mouse pointer to xlNorthwestArrow, why would you ever want that cursor when excel is in the middle of doing something?!?!

[Wim Gielis]

Hello Dubs

I'm sorry but I cannot reproduce what you wrote immediately above. For me this works nicely:

Code: Select all

Sub testje()

    Application.WindowState = xlMinimized

    Application.Run "TM1RECALC1"

    Application.WindowState = xlMaximized

End Sub

The screen goes away during the recalc (the mouse pointer is not changed but that is not needed IMO).

Wim

[dubs]

ah I was trying to be too clever and intercepting any sheet calculation, think wrapping each statement this way could do it (if the end users agree )

[dubs]

looks like this might be the only way to stop this, how very very annoying!

thanks for the suggestions guys

[dubs]

my final solution:

Code: Select all

  
  UserForm1.Show vbModeless
  DoEvents
  Application.Run "TM1RECALC1"
  UserForm1.Hide

using vbModeless arg because I want to display a label on the form but want the form to get closed by the .hide method, because a modeless form cannot show a label had to put the DoEvents statement in there (which i nicked off another forum)

anyway it doesn't solve the action button issue but certainly allows me to stop the cursor changing to a pointer whilst its doing a TM1RECALC1

[dubs]

so close yet so far... can control all calculations except when the action button has been clicked - don't think it is a bug but it is definitely a big oversight!

Iboglix 1 - Me 0