TM1 Perspectives interacts with other VBA code in Personal.xlsb
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
TM1 Perspectives interacts with other VBA code in Personal.xlsb
Hi all,
I come across a bizarre case. It's for Planning Analytics 2.0.6 but I have seen this in earlier versions too. I never bothered to find out the real cause. Now I do
In my Personal.xlsb I have a macro to open files I use on a regular basis. This macro can be launched by clicking an icon in the QAT (Quick Access Toolbar). This (small) macro uses a second procedure, where a password is asked from the user ( me ). The password is entered in a userform with a textbox since I want to hide the password - just in case someone is looking over my shoulder.
Now, opening Excel (therefore opening Personal.xlsb) works fine. Nothing happens initially, until I click the icon, I enter a password and a file is opened. But when I start Perspectives the macro in which the userform is shown also starts. Therefore, anytime I open Perspectives, I also get the question to enter a password to open a file I don't want to open.
How is it possible that opening Perspectives executes a macro in my Personal.xlsb ? It's not even a function but a procedure.
As soon as I have the code to .Show the userform, the macro is eligible for execution whenever Perspectives starts. No other VBA-code / addin is running at all.
I reduced all code to the below. Legend:
1: the icon to launch the macro in Personal.xlsb to open files I use often (macro: 'Open_My_Files' calling a second macro)
2: the userform pops up when I start Perspectives
3: the userform in VBA, nothing fancy
4: the code, reduced to the essential part, issue is still reproducible
5: the code for the userform. Other than a couple of variables, I have nothing there. I could even delete those given that in the Get_Password macro I commented out code as well.
Does anyone have an idea please ?
Happy festivities !
Wim
I come across a bizarre case. It's for Planning Analytics 2.0.6 but I have seen this in earlier versions too. I never bothered to find out the real cause. Now I do
In my Personal.xlsb I have a macro to open files I use on a regular basis. This macro can be launched by clicking an icon in the QAT (Quick Access Toolbar). This (small) macro uses a second procedure, where a password is asked from the user ( me ). The password is entered in a userform with a textbox since I want to hide the password - just in case someone is looking over my shoulder.
Now, opening Excel (therefore opening Personal.xlsb) works fine. Nothing happens initially, until I click the icon, I enter a password and a file is opened. But when I start Perspectives the macro in which the userform is shown also starts. Therefore, anytime I open Perspectives, I also get the question to enter a password to open a file I don't want to open.
How is it possible that opening Perspectives executes a macro in my Personal.xlsb ? It's not even a function but a procedure.
As soon as I have the code to .Show the userform, the macro is eligible for execution whenever Perspectives starts. No other VBA-code / addin is running at all.
I reduced all code to the below. Legend:
1: the icon to launch the macro in Personal.xlsb to open files I use often (macro: 'Open_My_Files' calling a second macro)
2: the userform pops up when I start Perspectives
3: the userform in VBA, nothing fancy
4: the code, reduced to the essential part, issue is still reproducible
5: the code for the userform. Other than a couple of variables, I have nothing there. I could even delete those given that in the Get_Password macro I commented out code as well.
Does anyone have an idea please ?
Happy festivities !
Wim
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 77
- Joined: Wed Sep 12, 2018 11:19 am
- OLAP Product: TM1 / Planning Analytics
- Version: 2.0
- Excel Version: 2016
Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb
Hi Wim,
This sounded like a pretty interesting problem to kick off the new year with, so I figured I'd give it a go. Unfortunately, I was unable to reproduce the issue you are encountering. The following are the things I've tried (please let me know if I didn't recreate a step correctly):
Config Information Attached
This sounded like a pretty interesting problem to kick off the new year with, so I figured I'd give it a go. Unfortunately, I was unable to reproduce the issue you are encountering. The following are the things I've tried (please let me know if I didn't recreate a step correctly):
- Opened Excel by itself - no auto execution of the form .Show property
- Opened the .xlsb file directly - no auto execution of the form .Show property
- Opened Perspectives directly - no auto execution of the form .Show property
- Opened Excel by itself, then opened Perspectives after in the same session - no auto execution of the form .Show property
- Tweaked all possible "Macro" settings in the Excel Trust Center (under Options) - no auto execution of the form .Show property
- Moved the code into an add-in file (.xlsa) - no auto execution of the form .Show property
- Windows 10
- Microsoft Excel for Office 365 MSO (16.0.11001.20064) 32-bit
- No add-ins or macros set to load on startup ("clean" Excel install)
Config Information Attached
- Attachments
-
- Capture.PNG (76.73 KiB) Viewed 8977 times
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb
Hello bgregs,
Thank you for your interest. Indeed, strange case
I can confirm that the setup is similar. So it's either someting in Excel, either something in my Excel environment which is not a macro or addin.
I will look at it further once I get to it. Please don't throw away your testing environment if you can, I might ask a few questions for verification.
I have a new Excel version it seems, other than that it should be equal.
Thanks,
Wim
Thank you for your interest. Indeed, strange case
I can confirm that the setup is similar. So it's either someting in Excel, either something in my Excel environment which is not a macro or addin.
I will look at it further once I get to it. Please don't throw away your testing environment if you can, I might ask a few questions for verification.
I have a new Excel version it seems, other than that it should be equal.
Thanks,
Wim
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 77
- Joined: Wed Sep 12, 2018 11:19 am
- OLAP Product: TM1 / Planning Analytics
- Version: 2.0
- Excel Version: 2016
Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb
Not a problem! Feel free to send any verification requests over my way!
- gtonkin
- MVP
- Posts: 1198
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb
Hi Wim/bgregs, I have had some inexplicable issues in the past when loading the add-in when Excel start, i.e. configured under the Excel Addins, versus opening Excel then opening TM1p.xla to load Perspectives - From you initial post Wim, sounds like you are loadind TM1p.xla later - is that correct?
If so, have you tried adding it to Excels add-ins to see what happens when you start Excel?
If so, have you tried adding it to Excels add-ins to see what happens when you start Excel?
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb
You are right about the behaviour, George. But starting Perspectives together with Excel causes the same userform to appear.
It's easier for testing though since I just have to start Excel
It's easier for testing though since I just have to start Excel
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb
Update. It's not related to any particular userform. I reduced the issue to the following:
So, the icon in the QAT has the macro 'Open_My_Files' attached. That macro is just a Debug.Print of the current time.
Perspectives starts with Excel. This somehow executes the macro from the QAT icon, leaving the current time in the Immediate Window in VBA.
With Perspectives not loaded with Excel, this does not happen.
With the macro renamed to Sub Test() and the icon's macro unchanged, I get the error message (a beep) when Perspectives opens. Excel can't find the macro 'Open_My_Files' obviously. So I rename it back.
Go figure...
Perspectives executes a macro in one of the QAT icons. The QAT icon holds for all files, not just for a specific file.
I'll continue testing. I tend to agree that it's not related to the VBA-code but a relationship between Perspectives and a QAT icon.
Thanks,
Wim
So, the icon in the QAT has the macro 'Open_My_Files' attached. That macro is just a Debug.Print of the current time.
Perspectives starts with Excel. This somehow executes the macro from the QAT icon, leaving the current time in the Immediate Window in VBA.
With Perspectives not loaded with Excel, this does not happen.
With the macro renamed to Sub Test() and the icon's macro unchanged, I get the error message (a beep) when Perspectives opens. Excel can't find the macro 'Open_My_Files' obviously. So I rename it back.
Go figure...
Perspectives executes a macro in one of the QAT icons. The QAT icon holds for all files, not just for a specific file.
I'll continue testing. I tend to agree that it's not related to the VBA-code but a relationship between Perspectives and a QAT icon.
Thanks,
Wim
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb
Adding a second QAT icon, totally similar, does not execute a second macro:
- Renaming the main QAT icon macro (NEW_NAME) and deleting the second icon and code again, now executes this NEW_NAME macro.
- Removing the main icon in the QAT and closing Excel (saving Personal.xlsb always when asked for), restarting Excel and adding the icon again: next time the macro is still executed !
- Starting Excel with Shift pressed does not launch Perspectives (since it disables code in the Open event of a workbook) and therefore the debug macro is not executed.
Wim
- Renaming the main QAT icon macro (NEW_NAME) and deleting the second icon and code again, now executes this NEW_NAME macro.
- Removing the main icon in the QAT and closing Excel (saving Personal.xlsb always when asked for), restarting Excel and adding the icon again: next time the macro is still executed !
- Starting Excel with Shift pressed does not launch Perspectives (since it disables code in the Open event of a workbook) and therefore the debug macro is not executed.
Wim
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb
The tension builds... Nearly got this one solved.
By playing around with the QAT icons (and reordering them) it appears that an Alt-8 is executed when Perspectives is opened.
This executes the macro attached to the 8th QAT icon. If I reshuffle icons and put a different icon in the 8th spot, that one gets executed.
On my keyboard, Alt-8 is done on the numbers at the top of the keyboard, not the numeric portion of my keyboard. Here's a picture showing that it's the 8 entered as Shift-!
my keyboard
Wim
By playing around with the QAT icons (and reordering them) it appears that an Alt-8 is executed when Perspectives is opened.
This executes the macro attached to the 8th QAT icon. If I reshuffle icons and put a different icon in the 8th spot, that one gets executed.
On my keyboard, Alt-8 is done on the numbers at the top of the keyboard, not the numeric portion of my keyboard. Here's a picture showing that it's the 8 entered as Shift-!
my keyboard
Wim
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb
I found the root cause !
Perspectives tries to be helpful and selects the TM1 ribbon. The VBA-code creates the ribbon for TM1 with the name !TM
Therefore pressing Alt, then the ! key and Enter will activate that ribbon.
But if on my keyboard this is Alt-8 it executes a different macro, the one attached to the 8th QAT icon.
File a bug with IBM.
Wim
Perspectives tries to be helpful and selects the TM1 ribbon. The VBA-code creates the ribbon for TM1 with the name !TM
Therefore pressing Alt, then the ! key and Enter will activate that ribbon.
But if on my keyboard this is Alt-8 it executes a different macro, the one attached to the 8th QAT icon.
File a bug with IBM.
Wim
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- Steve Rowe
- Site Admin
- Posts: 2415
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb
Nice detective work Van der Valk!
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb
It took a while but I got there
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 77
- Joined: Wed Sep 12, 2018 11:19 am
- OLAP Product: TM1 / Planning Analytics
- Version: 2.0
- Excel Version: 2016
Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb
WOW!!! Great job on digging that one up! I'm honestly surprised that this is the first time it's come up (with the variety of keyboards/layouts that exist in the wild), but either way, I guess we can look forward to an update in the next fix pack Nicely done!
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb
Thanks bgregs.
The bug has been raised with IBM. Feedback was received after a couple of hours, yet it was useless (the numlock thing and so on).
Wim
The bug has been raised with IBM. Feedback was received after a couple of hours, yet it was useless (the numlock thing and so on).
Wim
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 119
- Joined: Mon Oct 27, 2014 10:50 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2013
Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb
So... I'm not helping in any way.
But that was some impressive detective work. Really glad you're a part of the community.
But that was some impressive detective work. Really glad you're a part of the community.
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb
Thanks for the nice comments, you're welcome.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb
Status: this case is now forwarded as a defect to development:
"PH08270 TM1 tab in Excel can not selected with Keyboard shortcuts"
"PH08270 TM1 tab in Excel can not selected with Keyboard shortcuts"
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb
Closed by Development, no plans to change the currenjt behavior.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly