- Jan. 20, 2014
TM1 action buttons allow the user to do several actions:
- using a custom navigation with action buttons linking to different reports or input sheets
- executing 1 Turbo integrator (TI) process, or fire off a chain of TI processes
- recalculating a sheet
- refreshing a sheet, for instance, make a selection and rebuild an Active form
Action buttons are linked to a TM1 server name. In the past, this link used to be hard-coded: every Action button was configured to have a TM1 server name. In later versions of TM1, the developer has the option to use a cell reference for the server name, rather than a hard-coded name. Moving TM1 objects, including spreadsheets, to a different TM1 server name became less of a pain in the backside because updating the cell reference was sufficient.
The screenshot above, showing 2 very basic Action buttons to run a TI process, has a hard-coded TM1 server name. It is called "TI processes". This article will focus on changing properties of Action buttons through Excel VBA: we can automate this to a certain extent. Imagine a cover page with 30 Action buttons; it will not be pleasant to change 30 times the TM1 server name. Nor is it fast to change some of the other properties an Action button can expose.
First off, you will need to add a reference to a library: Applix TM1 Cube Viewing Controls for Excel. (see Tools > References in the VBEditor).
Sub TM1_LoopThroughActionButtons()' Wim Gielis ' http://www.wimgielis.comDim obj As OLEObject Dim tib As TM1XlCubeView_1_4.TIButton For Each obj In ActiveSheet.OLEObjects If obj.progID = "TM1XL.TIButtonCtrl.1" Then Set tib = obj.Object MsgBox tib.Caption End If NextEnd Sub
As you can see, once you know the library and the type of object to access, it is not too difficult to for instance, read the captions. See code above, where I put them in a MsgBox. You can also choose to not add the Reference to the desired library, and thereby work "Late binding" instead of "Early binding". In the former case, you will declare your variable as a generic Object type. Or as a Variant and save yourself from typing. When you do not use "Early binding" you cannot take advantage of the Intellisense in the VBEditor.
This works just as fine and the code is more condensed:
Sub TM1_LoopThroughActionButtons_2()For Each obj In ActiveSheet.OLEObjects If obj.progID = "TM1XL.TIButtonCtrl.1" Then MsgBox obj.Object.Caption End If NextEnd Sub
To use Intellisense, add the aforementioned reference and type tib (your variable name) followed by a dot:
This method allows you to search for the desired properties or methods. For example, to make the TM1 server name dynamic and set it equal to a defined name called "TM1server" (please note the use of With…End With to conveniently shorten the code and turn the code into a more readable format - at least to me):
Sub TM1_ChangeServerNameOfAllActionButtons()For Each obj In ActiveSheet.OLEObjects If obj.progID = "TM1XL.TIButtonCtrl.1" Then With obj.Object .UseReferenceForServerName = True .ServerName = "=TM1server" End With End If NextEnd Sub
The result of running the few lines of code is above. With more than a handful of buttons this code (and variations thereof) is a real timesaver. Needless to say, there are other properties and methods that you can control in a programmatic way. See the list below to see some of them.
With the information on this page, you could now tackle the situation where 100 Action buttons need to have a (customized) success message and failure message. Modify the code above by using the correct properties and you are done in a nick of time.