TM1 Perspectives interacts with other VBA code in Personal.xlsb

[Wim Gielis]

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

04.png (158.7 KiB) Viewed 11883 times

[bgregs]

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):

• 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

Testing Environment:

• 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)

This leads me to believe that it must be something outside of your VBA code. I'm thinking it's some obscure Excel setting that was set that may be throwing it off. Sorry I couldn't help further, but hopefully this helps in some way!

Config Information Attached

[Wim Gielis]

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

[bgregs]

Not a problem! Feel free to send any verification requests over my way!

[gtonkin]

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?

[Wim Gielis]

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

[Wim Gielis]

Update. It's not related to any particular userform. I reduced the issue to the following:

04.png (247.89 KiB) Viewed 11799 times

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

[Wim Gielis]

Adding a second QAT icon, totally similar, does not execute a second macro:

06.png (230.71 KiB) Viewed 11795 times

- 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

[Wim Gielis]

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

[Wim Gielis]

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.

06.png (66.62 KiB) Viewed 11784 times

Wim

[Steve Rowe]

Nice detective work Van der Valk!

[Wim Gielis]

It took a while but I got there

[bgregs]

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!

[Wim Gielis]

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

[Bakkone]

So... I'm not helping in any way.

But that was some impressive detective work. Really glad you're a part of the community.

[Wim Gielis]

Thanks for the nice comments, you're welcome.

[Wim Gielis]

Status: this case is now forwarded as a defect to development:
"PH08270 TM1 tab in Excel can not selected with Keyboard shortcuts"

[Wim Gielis]

Closed by Development, no plans to change the currenjt behavior.