I am new to this forum, but have problem I like to bring forward to you.
My application uses a Excel template and a dedicated Addin. Most code is stored in the Addin. The application is multi-lingual. I have build a custom menu according to examples from the book Excel 2002 VBA Programmers Reference => using a menu table.
All works well, the custom menu is build when opening the standard template/workbook and removed when the Addin/workbook is closed.
The problem I am having is when I try to rebuild the custom menu structure within the application (the custom menu is placed on the main worksheet menu bar). This is required when the user selects a different language => the menu should be translated.
The standard code to remove the existing menu bar is called, but the menu is not deleted, it stays put. Instead it adds the translated version of each menu item on top of the existing ones.
If I use a loop to delete all custom menu entries having a unique TagID, the loop goes on indefinitely. The For Next loop should stop when the routine does not find the unique TagID string and the controlbar should then become 'Nothing'. However, this condition does not appear to occur. The strange thing is that when I break into the code, than the loop is exiting normally. The controlbar object is now suddenly set to 'Nothing' and the custom menu is deleted.
1). This behaviour only occurs when I re-run the setup of the menu system when selecting a different language translation. The code used for the menus is exactly the same. The menu is properly removed when I close the Addin without rebuilding it after the inital setup.
2) When selecting a different language, a routine gets translated text from a separate (closed) workbook using the ExecuteExcel4Macro function. The menu translations are copied to the MenuTable and after that the routine for setting up the menu is called to rebuild the menu.
* why the custom menu is not deleted?
* what makes the code not set the controlbar object to 'Nothing' (and therefore does not leave the loop) under normal execution in the For Next loop?
A very strange thing I can not put my hand on. Any ideas are welcome!