VBA Modification Buttons

[christophe_s]

Hello,

Our TM1 application was builded using a lot of Excel Files (hundreds) , I have to modify all the buttons to remove "Match titles Elements" and fill the map with our data.

I would like to do do it by macro to avoid mistakes and the bug of changing the size of the button...

I found the code to access and modify buttons proprieties ( see below) but I don't find how to add into the map( SourceTypes, SourceObjects, TargetTypes, TargetObjects)


Set ws = ActiveSheet
For Each obj In ws.OLEObjects

s = obj.progID
If InStr(s, "TM1XL.TIButtonCtrl") > 0 Then
s = obj.Name
Set tm1button = ws.OLEObjects(s).Object
If tm1button.DoNavigate = True Then

tm1button.AutoTitles = False

tm1button.SourceTypes = "2" & vbCr & "2"
tm1button.SourceObjects = "=Program" & vbCr & "=Workspace"


tm1button.TargetObjects = "Program" & vbCr & "Workspace"
tm1button.TargetTypes = "1" & vbCr & "1"


End If
End If
Next


Could you help me to modify the mapping ( tm1button.SourceTypes, tm1button.SourceObjects, ...)?

Regards
Christophe

[rmackenzie]

Prepare to proceed with caution as I've seen programmatic updates of action buttons totally corrupt their properties leading to greater headaches than the original problem... however, putting that to the side, I bet you a beer that using vbCr isn't going to work. I'd be tempted to try other delimiters e.g. ASCII 0 (vbNull?) or ASCII 127. Did you try and export the existing setting and look at it with a hex editor to understand what TM1 uses by default? Beware it may differ from version to version...