TM1 context menu in cooperation with Personal.xlsb

[Wim Gielis]

Hi all,

We encounter a strange case at one of our customers.
TM1 version 10.2.00205 in a Cognos Express setting. Excel 2013.

The user has 1 module with 1 macro in his Personal.xlsb file. The macro is just a way to quickly format the result of a drill-through to a different cube view.

In Excel the drill is started from a DBRW, a new file opens. All is good.

But in the new file there are no TM1 items in the context menu (right mouse button): no Active form, no drill, no data spread and so on.

If on the other hand the Personal.xlsb file is taken out of the XLSTART folder for the user, the drill works fine and in the new file all normal options are there in the context menu.

The macro is really simple and no event takes please. It's the result of using the macro recorder for a while. Changing the VBA code with MsgBox gives the same behaviour.

Does someone recognize such behaviour please?
As I cannot reproduce it, going to IBM will be a waste of time I guess.

Thanks !

[gtonkin]

Hi Wim,
Is all the other TM1 functionality in the new workbook working as expected i.e. not running in a new instance of Excel? Can only think it must be down to the way the new workbook is instantiated.

Just tried calling a basic macro do try and replicate what you are doing:

Code: Select all

Sub CreateNewWB()
Dim NewWB As Workbook

    Set NewWB = Workbooks.Add
    Debug.Print NewWB.Name
End Sub

Once the new workbook is created, I still have the expected option on right-click although Data Spread and Holds are greyed as there are no relevant areas.
I did notice that there are some events that are triggered when adding the new workbook. Are you/user disabling events before adding? Could you share the macro code?
I am running this on 10.2.2 FP4

[Wim Gielis]

Hello,

Thank you for your reply and tests.

I did the test with TM1 10.2.2.4 and TM1 10.2.0 and I cannot reproduce it, even with the same macro. All Excel 2013.

The ribbon is still functioning, but the options in the context menu do not show up.

No events take place or are disabled. The macro is macro-recorder generated code:

Code: Select all

Sub Macro1()
'
' Macro1 Macro
' TM1 subtotalen detail overheadsheet
'
' Sneltoets: Ctrl+b
'
    Cells.Select
    Range("B10").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.EntireColumn.Hidden = False
    Selection.EntireRow.Hidden = False
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1:A9").Select
    Selection.EntireRow.Delete
    Columns("E:F").Select
    Selection.Delete Shift:=xlToLeft
    Range("A9").Select
    ActiveCell.FormulaR1C1 = "x"
    Range("B9").Select
    ActiveWindow.WindowState = xlMaximized
    ActiveCell.FormulaR1C1 = "SITE"
    Range("C9").Select
    ActiveWindow.WindowState = xlMaximized
    ActiveCell.FormulaR1C1 = "MAAND"
    Range("D9").Select
    ActiveWindow.WindowState = xlMaximized
    ActiveCell.FormulaR1C1 = "DATUM"
    Range("E9").Select
    ActiveCell.FormulaR1C1 = "ANA"
    Range("F9").Select
    ActiveCell.FormulaR1C1 = "ANA NAME"
    Range("G9").Select
    ActiveCell.FormulaR1C1 = "ALG REK"
    Columns("I:M").Select
    Selection.Insert Shift:=xlToRight
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    Columns("R:T").Select
    Selection.Cut
    Range("I1").Select
    ActiveSheet.Paste
    Columns("V:V").Select
    Selection.Cut
    Columns("L:L").Select
    ActiveSheet.Paste
    Columns("U:U").Select
    Selection.Cut
    Columns("M:M").Select
    ActiveSheet.Paste
    Columns("O:O").Select
    Selection.Insert Shift:=xlToRight
    Columns("R:R").Select
    Selection.Cut
    Columns("O:O").Select
    ActiveSheet.Paste
    Rows("9:9").Select
    Range("D9").Activate
    Selection.Font.Bold = True
    Columns("P:P").Select
    Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Cells.Select
    Range("D1").Activate
    With Selection.Font
        .Name = "Calibri"
        .Size = 6
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Cells.EntireColumn.AutoFit
    ActiveWindow.LargeScroll ToRight:=-1
    Range("A9").Select
    Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(16), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Selection.AutoFilter
    ActiveSheet.Range("$A$9:$Q$29").AutoFilter Field:=3, Criteria1:="=TOTAAL*" _
        , Operator:=xlAnd
    Cells.Select
    Selection.Font.Bold = True
    ActiveSheet.Range("$A$9:$Q$29").AutoFilter Field:=3
    Columns("P:P").Select
    Columns("P:P").EntireColumn.AutoFit
    Columns("B:B").ColumnWidth = 2.86
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = "&Z&F"
        .CenterHeader = ""
        .RightHeader = "&A"
        .LeftFooter = "&D"
        .CenterFooter = "Pagina &P van &N"
        .RightFooter = "&T"
        .LeftMargin = Application.InchesToPoints(0.118110236220472)
        .RightMargin = Application.InchesToPoints(0.118110236220472)
        .TopMargin = Application.InchesToPoints(0.354330708661417)
        .BottomMargin = Application.InchesToPoints(0.354330708661417)
        .HeaderMargin = Application.InchesToPoints(0.118110236220472)
        .FooterMargin = Application.InchesToPoints(0.118110236220472)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub

Attached is the PERSONAL.XLSB, but renamed. It should go to %AppData%\Roaming\Microsoft\Excel\XLSTART

Even with this exact file I cannot reproduce the issue but to be sure, I saw the defect in action myself

Thanks.