Rebuild Current Sheet Funktion

[ckoenig]

Hello Community,

actual i am using an active sheet based on IBM COGNOS Perspective 10.2.2 Version and Office 2010. Within this active Form I want to us the Funktion "Rebuild Current Sheet" without click into the sheet. What i need is similar to the automatic Recalc F9 Function.

Is this possible ? Hope someone can help me.

Chris

[qml]

You can use a bit of VBA to achieve that.

Combine Application.OnKey with Application.Run "TM1REFRESH".

[ckoenig]

Hello
can you give me an example how i can combine Application.OnKey with Application.Run, that i can paste it into my macro area from Excel Active Form ?

[qml]

Hello
can you give me an example how i can combine Application.OnKey with Application.Run, that i can paste it into my macro area from Excel Active Form ?

I think you will benefit more if you try and do it yourself. You only need a few lines of code and with the hints from me and a bit of googling you should be able to work it out. If you get stuck, come back here and we'll be happy to help out with any specific problems - but personally I do not think it is reasonable to expect others to write your solution for you.

[ckoenig]

sorry but i dont understand why i should use Application.OnKey . Isn it that i need again a click or somthing and it is not calcualted automatic during add a new value for example ?

[qml]

sorry but i dont understand why i should use Application.OnKey . Isn it that i need again a click or somthing and it is not calcualted automatic during add a new value for example ?

You wrote this in your OP:

What i need is similar to the automatic Recalc F9 Function.

...which I took to mean that you want to be able to press a key in order to rebuild an Active Form. If my interpretation of your terse requirement is incorrect then all you need to do is change the OnKey event suggested by me to another Excel event of your liking.

[gtonkin]

Also not sure of what exactly you are trying to do but assuming you want to rebuild the ActiveForm with a short-cut key similar to F9, try Alt+F9.
Alternatively, please give more details to allow members to help you.

[ckoenig]

OK i will try to describe it from scratch in more details.

I have an active Form
STEP01: first formula is a SubNM in Cell C11
=SUBNM("greatoutdoors:Income Statement Accounts";"";"OPERATING INCOME")

Step02: second formula is verketten(concadinate) in Cell F8 picking up the selected elementname from Cell 11
=VERKETTEN("{[income statement accounts].[";$C$11;"],drilldownmember([income statement accounts].["; $C$11;"].children, {[income statement accounts].["; $C$11; "].children})}")

Step03: Now in the cell B14 you can see the formula from Active Sheet using the whole MDX stored in Cell F8
=TM1RPTROW($B$6;"greatoutdoors:Income Statement Accounts";"";"";"";1;F8)

Pressing "F9" will refresh only Step 01 and Step 02 but not Step 03:
If i want to refresh also Step 03 i must use additional "Rebuild Current Sheet"

I can change F9 in Exel Options from manual to automatic. If i change now a value in Cell 11 ( SUBNM) F9 will refresh my value within MDX Cell F8 automaticaly

My original Question from my first post was now:
How i can refresh step 3 ("Rebuild Current Sheet") togehter with Step 01 and 02 without click "Rebuild Current Sheet" manual.

Hope you understand my Question and someone can help me find a Solution for it.

Chris

[tomok]

The only way to rebuild an active form in Perspectives (which is what you are asking for) is to 1) hit Alt-F9 on your keyboard, 2) insert an action button on the sheet which rebuilds upon click, or 3) write a VBA macro that calls the TM1REFRESH function and tie that to some sort of event.

[ckoenig]

This Makro i have tested but if i add this makro my SUBNM doesnt work anymore ?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Run "TM1Refresh"
End Sub

[Wim Gielis]

Why not a Worksheet_Change event ?
Do you really want / need that your sheet is rebuilt any time you click on a cell, use the arrow keys, pageup/down, etc. ?

[lotsaram]

Pressing "F9" will refresh only Step 01 and Step 02 but not Step 03:
If i want to refresh also Step 03 i must use additional "Rebuild Current Sheet"

I think there is some misunderstanding as to what you want
- The sheet should rebuild the active form automatically although in manual recalculation mode when a SUBNM is changed?
- You just don't want the user to need to use the toolbar or click a button and be able to use keystroke instead for the rebuild?

Reading your original post I was convinced it was the 1st option (in which case you would need VBA and the Worksheet_Change event but reading your latest post I now think that you just want the 2nd option, in which case gtonkin already gave you the answer. TM1 Perspectives has a built-in accelerator key for Application.Run "TM1Refresh" it is Alt + F9.