Page 1 of 1
TM1 context menu in cooperation with Personal.xlsb
Posted: Thu Mar 17, 2016 6:39 pm
by 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 !
Re: TM1 context menu in cooperation with Personal.xlsb
Posted: Fri Mar 18, 2016 5:31 am
by 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
Re: TM1 context menu in cooperation with Personal.xlsb
Posted: Fri Mar 18, 2016 7:13 am
by 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.