wimgielis.com

Action buttons

• Jan. 20, 2014

Introduction

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.

VBA-code

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
' https://www.wimgielis.com
'''''
' Action buttons - programmatic access
' 01/20/14
'''''
    Dim 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
    Next

End 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
    Next
End 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
    Next
End 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.

• « Previous TM1 article
• Next TM1 article »