Page 1 of 1
TM1 Perspectives - Prompt when closing Excel?
Posted: Fri Dec 14, 2018 4:00 pm
by jamesbennett
I run TM1 through Perspectives. This means that if I mistakenly close all open Excel workbooks, all my TM1 windows close as well. Is there an easy way to force Perspectives to warn me when I try and close Excel with Perspectives still running?
I'm using Perspectives 10.2.2 with Excel 2013.
Re: TM1 Perspectives - Prompt when closing Excel?
Posted: Fri Dec 14, 2018 5:22 pm
by jim wood
If I remember rightly, if you changed a view it should prompt you to see if you want to change the view? Beyond that I'm not sure if you can as it is an Excel add-in after all,
Jim.
Re: TM1 Perspectives - Prompt when closing Excel?
Posted: Fri Dec 14, 2018 8:54 pm
by bgregs
A few ideas come to mind, but nothing pretty. Excel will prompt you to save an open workbook if you have made a change to it, which means we could potentially use that in our favor through one of two options:
A) Put a space or some random character into a cell of the blank workbook that opens on startup
B) Create your own add-in that loads on startup that automatically puts a random character and removes it from a new workbook
Both of these will change the "state" of your Excel workbook from "new" to "updated", which will force a prompt.
Re: TM1 Perspectives - Prompt when closing Excel?
Posted: Fri Dec 14, 2018 9:33 pm
by Wim Gielis
Natively and out of the box: no.
You would need to add VBA code to intercept events, like Workbook_Close, Workbook_Deactivate.
Actually Application level events. Knowing that TM1 hijacks a number of events too, this will not be easy at all.
Re: TM1 Perspectives - Prompt when closing Excel?
Posted: Mon Dec 17, 2018 3:40 pm
by jamesbennett
Thanks all for the ideas. It's useful to know there is no out-of-the-box solution. I think the VBA route might be best - will give that a try!
Re: TM1 Perspectives - Prompt when closing Excel?
Posted: Mon Dec 17, 2018 4:09 pm
by tomok
Closing all open Excel workbooks does not cause open TM1 boxes to close. I just verified that. Closing Excel itself does cause all open TM1 objects to close. What do you expect? Perspectives is an Excel ad-in. No Excel then no Perspectives. Also, if you have changed a view, or subset, then you will automatically get warned when you exit Excel. If you have not changed the view then why do you care? It'll still be there in the "saved" state the next time you open Perspectives. I don't know what you are trying to accomplish.
Re: TM1 Perspectives - Prompt when closing Excel?
Posted: Mon Dec 17, 2018 5:16 pm
by bgregs
Hi Everyone,
Maybe I'm misunderstanding, but this is extremely easy to do with a custom add-in (however I agree that I don't quite understand why this would be needed). Create a new module in a blank workbook and put the following code in there:
Code: Select all
Private Sub Auto_Open()
Dim wb As Workbook
Set wb = Workbooks.Add()
Worksheets("Sheet1").Range("A1").Value = 1
Worksheets("Sheet1").Range("A1").Value = ""
End Sub
Then save it as an Excel-Addin format and modify your File > Options > Add-ins to load your new add-in on startup (you will need to browse for it). From here on out, Excel will ALWAYS prompt you before you are allowed to close it completely.
Again, not sure why this would be super useful, but I can confirm that this simple macro does the trick (it will load on default, so opening Perspectives directly will still trigger this event)!
