I am trying to work out a solution to share most of my VBA codebase across workbooks. This involves creating a .xlam file that would contain the shared modules & classes (+ their respective factories).
I'm willing to use Office 2007's wonderful RibbonUI, and have that .xlam file declare a custom tab (Let us call it MyTab for the sake of the argument). I need MyTab to only display some elements if the user has specific permissions (which I manage through VBA).
Besides that, I want my other workbooks to be able to add stuff into MyTab, which may also have dynamic elements (that use VBA).
The issue I'm facing is that whenever I get in the case where a workbook references the AddIn.xlam and declares dynamic ribbon elements, Excel tries to refresh all at once, thus calling VBA code in two separate threads (I guess), which eventually fails (because VBA is single-thread-forced in Excel), and generates blank dialog boxes with just "400" written on those and two buttons "OK" and "Help".
Has someone got experience on how to best tackle this issue?