Problems deleting custom menu => double entries

[BenD]

I am new to this forum. I have seen some very good support from you
guys & girls so I thought I might give it a try. For your info, I also
posted this in the ozGrid forum.

My application uses an Excel template, a dedicated Addin and a file
containing translations. Most code is stored in the Addin (including
the code for setting up the menus). The application is multi-lingual.
I have build a custom menu according to examples from the (excellent)
book Excel 2002 VBA Programmers Reference => using a menu table.

All works well, the custom menu is build (and put before the 'Windows'
menu option of the standard Worksheet menu bar) when opening the
standard template/workbook and removed when the template/workbook is
closed.

The problem I am having is when I try to re-build the custom menu
structure within the application. This is required when the user
selects a different language => the menu should be translated.
The common approach is to delete the existing menu first. The standard
code to remove the existing menu bar is called, but the menu is not
deleted, it stays put. Instead it creates an additional menu caption,
while it adds all the controls of the translated version of each menu
item on top of the existing ones (because the FindControl will still
find the previous version).
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
in the following loop suddenly set to 'Nothing' and the custom menu is
deleted. Without breaking into the code this condition does not
appear.

Notes:
1). This behaviour occurs when I re-run the (same) setup function of
the menu system when selecting a different language translation.
2). In the mean time I discovered an even weirder thing about this
problem. The code for selecting a new language and translating the
menu and worksheet content runs from a Commandbar ComboBox in the
menu. If I run the SAME function (that takes care of the translation)
from a Commandbar Commandbutton control everything works fine! So now
the same routine that should delete the existing custom menu does find
it and deletes it. Running the function from the Combobox control
again does not and thus duplicates the menu.

So it really looks like the routine is not to blame (I use exactly the
same) but the control where it is called from. I just can not put my
finger on it (and it drives me mad)!

I can post the code coming from the mentioned book here if requested.

Any ideas from the forum? Your inputs are welcome!
(Note: I only have access to the Internet from my office....).

 

[Jim Rech]

Apologies if I read your post too quickly but my immediate thought is that
you are trying to delete a commandbar by running code started by a control
on the commandbar. I don't think that will work. Commandbars do not commit
suicide! You might try calling the core delete macro from an OnTime macro
initiated by a commandbar button click.

Sub ClickHandler()
Application.OnTime DateAdd("s",1,Now), "RedoCmdBars"
End Sub

Sub RedoCmdBars()
....
End Sub

This would break the connection.

Btw, I think if I were doing this I'd see if just changing the controls'
captions would get the job done.

 

[BenD]

Hi Jim,

Thanks for your feedback!
Your statement regarding the fact that Commandbars don't commit suicide
had crossed my mind also!

I will certainly give your suggestion a try.

I like your suggestion of only changing the captions. Why did I not
think of that....? So far I thought it would be easier to just use the
existing code to set up the menus from scrath (whithout writing
additional code). Do need to see if I don't run into difficulties with
the submenus, but this should not a big issue.

Although I am sure at least one of your suggestions should work, I do
however feel it is strange that the same code runs smoothly when run
from a standard CommandButton, while it does not when run from a
ComboBox control. Would be nice to understand the logic behind that....

Regards,

Bennie Douma

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!