Running VBA macro when workbook opens but only AFTER the ribbon and custom tab has loaded.

Hi,

So this is a symptom of a very common issue with a program/add-in called IBM Cognos TM1. Basically it installs and it's an add-in for Excel which allows you to pull data into your sheets.

The issue is that when the extra tab initializes/loads it turns off the numlock on the computer. This is a known bug that they've tried to fix in newer versions and yet it still happens. The cause is believed to be multiple send key commands within the add-in coding which disables the numlock, which is also a common Excel bug in 2007/10.

So, I've tried to fix this issue but I need a little bit more help.

I inserted some VBA code into the workbooks_open event to get Excel to turn the numlock on whenever the specific file opens, and this works great in blank files with no add-ins; however, when it is used with the actual intended file with the TM1 add-in enabled, it doesn't work.

The reason is that the custom tab from TM1 which ends up disabling the numlock, only loads once all the workbooks_open code has been executed. If I attempt to delay or sleep and then have the numlock command run, this doesn't work either; the custom tab's load is also delayed until the workbooks_open code has run and then loads, turning the numlock back off.

So basically any code in the workbooks_open event is undone because the custom tab which causes the numlock to turn off, always loads after that code and causes the issue.

Unfortunately, the add-in is password protected so I can't access it's code to fix the actual cause.


So here's my question: Is there a workbook event which I can use or something I can do to make a specific macro or piece of code run when a workbook is opened but only AFTER the ribbon has fully loaded with any custom tabs?

Thanks for the help,

Richard

I tried that but then the custom tab also loads later, as if its waiting for all workbook_open code execution to be finished.

So I just realized another problem now. Thanks for the code it works great except in this case for a reason I'm not sure I can solve. Apparently, if you click on any cell before the custom tab has finished loading, then the getkeystate function reverses the stated state of the key. So for example, if I let it fully load, then it turns the numlock on. If I pre-empt it finishing and click on a cell, then when it gets the key state, it says the key is off even when it is actually on and so while it keeps trying to turn it on, it actually turns it off. I hope this makes sense although the actual thing happening makes no sense to me. Is there a way to reset the keyboard state? If I leave excel and then open the file again and give it time to load then it won't give me any problems.

Richard

That is weird. I have no answer for that since I can't test it. Perhaps delay the initial on launch call for a little while. You could test if the addon is loaded and send an assumptive numlock press instead of actually testing the key state. The other thing that might work would would be a really dirty hack. You could have it select a cell you know is blank, sendkey a specific numpad key, and test if the cell value is the number expected or not. There are cleaner ways to send a keypress than sendkey but it should work.

Actually.. maybe it is counting a pressed down state for some weird reason. Try changing the function to the following one.

Please Login or Register  to view this content.

This will only return true if it is both on and NOT being held down.

Hey guys,

So I tried a number of things and different methods which did not work. I downloaded a 3rd party program that allowed me to open the TM1 add-in without the password so I could look at the code. The issue was as I thought that several sendkeys were used resulting in turning off the numlock. Also, unfortunately, the last sendkey also included a wait command which pause any and all additional vba coding and thus defeated the ontime command as well because it seemed to stop the timer (I used debug.print and a counter to see the loops happening after each second and they would stop mid way until you click somewhere and then would restart).

So anyway, my final solution was this:
Using the 3rd party software to temporarily disable the password protection, I went in, found the sendkeys commands, took out the 'True' which told it to wait for input and then added a sendkey "{numlock}" at the end so that it enabled the numlock after all those sendkeys. Now it works great, which is hilarious that one line of code has fixed an issue with this software that people have been complaining of for over a year at different companies and forums.

The only caveat is that I have to get permission to push this version of the add-in file to all users that have it installed and into their install directories (it relies on other files within that directory), so we'll see if IT will let me do this :P.

Thanks for all your help!