I need to detect from within an excel-addin when a userform is loaded into memory without using the Initialize- or Activate-event of the loaded userform.
I'm currently trying to extend an Excel app by a translation addin to provide for the users a multilanguage GUI. Up to now, I could manage to translate on the fly all visible text-entries on (work-)sheets including comments, screentips and form- or ActiveX-controls embedded in shapes located on the sheet by passing the sheet object to a class which examines the sheet and its objects for text-entries needing translation and translate the identified text entries.
The next challenge will be to design a similar class for examination of userform-text-entries to be translated. Unfortunately, Excel does not provide a method to check upfront if and how many userforms can be loaded other than querying the VBE designer. In my case, VBE has to be locked and password protected so VBE cannot be used for querying for userforms. In addition, the Initialize and Activate events of the main application userforms should not be changed.
Due to my observations, the userforms collection is only populated when the userform is loaded and if the userform is an object of ThisWorkbook, so userforms designed within an additional addins are not part of the main application userforms collection.
The only chance I currently see, is to provide an event when a userform instance is loaded into memory causing a ThunderDFrame class window to pop up within the Excel window tree.
which then can be used for passing the userform-object to the examination routine.
Any idea how to achieve this within VBA? I assume, it might be possible by subclassing the Excel application with the API function SetWinEventHook to detect ThunderDFrame class window creation but my programming skills are not sufficient to figure this out on my own.
Any other approach is also welcome.
