VBA: Problems deleting custom menu => double entries

BenD · Oct 17th 2003

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.

Notes:
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.

Any ideas:
* 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!

Richie(UK) · Oct 17th 2003

Hi Ben,

Welcome to the forum

From memory that code contains a constant at the start in respect of the name of the menu. Are you also translating this? I believe that this reference should remain unchanged.

(I don't have the code available at the moment but, if the above doesn't resolve the issue I'll try to have a look over the weekend)

BenD · Oct 17th 2003

Hi Richie, thank you for the very quick reply & welcome!

You are right, your memory serves you well. The TagID is a constant. But I do not change/translate it, it remains the same, only the captions of the menu items are translated. As the FindControl method uses the TagID string as key, finding the right menu should be the same.

The funny thing is that if the FindControl would not "find" anything, it should return 'Nothing' and leave the loop immediately.

If you can digg anything up over the weekend I appreciate it!

BenD · Nov 7th 2003

For logging purposes:

The problem is not really solved, but a workaround is to not re-build the menu, but only translate the captions of the menu items.

Further information on the original problem:
The rebuilding of menus work when activated from a standard command button on the menu bar. Performing the same action using a CombBox control results in the problem.

**Jack in the UK** · Nov 8th 2003

I have un ticked the "Un-Solved" to boost this feed.

Kind regards

Jack

**Jack in the UK** · Nov 8th 2003

You seem well on the way to solving this question, well done. Just as a point i have in the passed found that if some characters IE "&" are used in names or the string text naming is too long then the naming length is reduced, and can be a pain to remove later. Or will leave active blanks in its place. Better of the two i guess.

Kind regards

Jack

Pesky Weasel · Nov 9th 2003

BenD,

The particular solution you are using is one of my faves and almost siglehandedly justified the expense of the book for me.

I dont have any answers to your why, just an approach that may be worth considering. Have the control names for different languages set up in the menubar table and then at runtime change the Const miTable_CONTROL_CAPTION constant to a variable that references the particular column that contains the correct captions.

One thing to watch out for using this method is that you dont change the caption of the Type 10 (Drop Down Menu) control or the popups wont know who they belong to.......having said that you could always incude extra columns for the pop up names and change the Const that refers to them to a variable:

Code

At module level Dim iLang as Integer


In the SetupMenus code change like this:
       With cbBtn
            .Caption = vaData(1, iLang)
            .BeginGroup = vaData(1, miTABLE_CONTROL_GROUP)
            .TooltipText = vaData(1, miTABLE_CONTROL_TOOLTIP)


I used the blow test to see if it worked:
Sub chLang()
iLang = InputBox("Enter 5 for English and 15 for Japanese:")
RemoveMenus
SetUpMenus
End Sub

Display More

Regards
Weasel

VBA: Problems deleting custom menu => double entries

Participate now!

Share