Solved: Menu items created twice, but not when manually going through code

[BenD]

The following issue is making me lose hair....

To create a menu (part of the standard "Worksheet Menu Bar") for my application I am using the "Advanced Command Bar Handling" module from "Professional Excel Development" (by Stephen Bullen, Rob Bovey & John Green). Excellent stuff and it works really well.

Until...now.
The application I am developing needs to be multi-lingual. To be able to change the language for the menu items, I have written code that retrieves translations (only 8 menu items) from an Access database. During code execution, the old menu items (of the old language) that are stored in a menu table, are replaced with the menu items in the new language.
The issue I am having is that the resulting menu contains basically a double menu list, one in the old language, and one in the new (it is added underneath the original menu)!

Now the real hair-pulling begins: this behaviour does NOT occur when I (manually) toggle through the code; when I do that the old menu entries are perfectly removed and I am left with only the menu with the new language translations!!! Why is the big question!

A few pointers:

The custom menu I create is part of the "Worksheet Menu Bar" and placed to the left of the "Help" menu.
The name of custom menu does NOT get translated.
The "ResetCommandBars" routine from the module from Rob Bovey looks for the custom menu's caption and removes it if it is found. Because the name of the custom menu is not changed, replacing menu item captions does not influence the removal of the "old" menu.
I also tried to use a different custom menu name, by first calling the routine to remove the current custom menu (including the menu name), replace all text with new translations and generate the new menu. The only result is that I get 2 custom menus, just in different translations....
The code to delete and recreate the custom menu is called from a class module (I tested if calling "ResetCommandBars" from a standard module would make a difference, but this did not make a difference).I do not think it is necessary to publish the code from "Professional Excel Development".

The (somewhat simplyfied) code to replace the menu:




Public Function bTranslateApplication() As Boolean






If Not bTranslateMenu Then Err.Raise glHANDLED_ERROR
If Not bBuildCommandBars() Then Err.Raise glHANDLED_ERROR

' Set the current language in language Dropdown control:
If Not bSetMenuLanguageSelection() Then Err.Raise glHANDLED_ERROR





End Function



The code to replace the menu items in my menu table is the following:
Private Function bTranslateMenu() As Boolean
Dim bReturn As Boolean
Dim rsData As ADODB.Recordset
Dim rngRange As Range ' Range on command bar worksheet where language table is stored.
Dim sSQL As String
Dim sSQLLangField As String
Dim iLanguageID As Variant
Dim lRecCount As Long
Const sSOURCE As String = "bTranslateMenu()"
Const sRNG_MENU_ITEMS As String = "lstMenuItems"
Const sTBL_MENU_TRANSLATIONS_FIELD As String = "tblMenuTranslations.Language"
On Error GoTo ErrorHandler
bReturn = True
' Get a connection from the connection pool:
gcnConnection.Open

' Retrieve the languageID from the language table:
iLanguageID = LanguageID
' Create the string to select the correct field name:
sSQLLangField = sTBL_MENU_TRANSLATIONS_FIELD & iLanguageID
' Build SQL string:
sSQL = "SELECT" & gsSPACE & sSQLLangField & gsSPACE & _
"FROM tblMenuTranslations ORDER BY tblMenuTranslations.MenuID;"
'SELECT tblMenuTranslations.Language1 FROM tblMenuTranslations
'ORDER BY tblMenuTranslations.MenuID;

' Create the Recordset object to store the query:
Set rsData = New ADODB.Recordset

' Open a recordset based on a query that provides all current menu items:
rsData.Open Source:=sSQL, ActiveConnection:=gcnConnection, _
CursorType:=adOpenStatic, LockType:=adLockReadOnly, Options:=adCmdText
If Not rsData.EOF Then
' Determine the number of elements in the recordset:
lRecCount = rsData.RecordCount
If lRecCount > 0 Then
Set rngRange = wksCommandBars.Range(sRNG_MENU_ITEMS)
' Clear existing list with menu items:
rngRange.ClearContents
' Store the new list with menu items in current language:
rngRange.CopyFromRecordset rsData
Else
' No data could be retrieved, so provide an error message:
Err.Raise glHANDLED_ERROR, sSOURCE, msERR_NO_MENU_ITEMS
End If
Else
' No data could be retrieved, so provide an error message:
Err.Raise glHANDLED_ERROR, sSOURCE, msERR_NO_MENU_ITEMS
End If

ErrorExit:
rsData.Close
Set rsData = Nothing
Set rngRange = Nothing

' Close the database connection:
CloseDatabaseConnection
bTranslateMenu = bReturn
Exit Function

ErrorHandler:
If Err.Number <> glHANDLED_ERROR Then Err.Description = Err.Description & _
" (" & sSOURCE & ")"
bReturn = False
If bCentralErrorHandler(msMODULE, sSOURCE) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Function



I have tried many things to find out why it does what it does, but because it works perfectly well when manually toggle throught the code, it is very difficult to understand where the core of the problem lies.

Has anyone come across something similar and knows how to address this? Would be great if someone had, as I do not have that much hair left....

Edit by lucas: Ben I added line breaks to your code....some of us don't have huge monitors

[tpoynton]

I've come across this, and found the solution interesting enough to put in a KB entry (http://vbaexpress.com/kb/getarticle.php?kb_id=807) . while I am listed as the author of the entry, it is really the folks here that did the work. it's a completely different approach to getting the translations. I cant help figure out what might be wrong here though...

something else to be aware of: "Worksheet Menu Bar" is not available in many other language versions of Excel; you need to use the commandbar ID if the workbook will be opened in some other language versions of Excel

[Bob Phillips]

I have had problems with menu items not being removed when I am trying to update them. My solution was to put the code in a separate procedure, and call that procedure as a delayed action using Ontime.

[BenD]

I've come across this, and found the solution interesting enough to put in a KB Entry. while I am listed as the author of the entry, it is really the folks here that did the work. it's a completely different approach to getting the translations. I cant help figure out what might be wrong here though...

something else to be aware of: "Worksheet Menu Bar" is not available in many other language versions of Excel; you need to use the commandbar ID if the workbook will be opened in some other language versions of Excel

Hi tpoynton,

Thanks for pointing out the potential issues with different Excel language versions. I was aware of this, but was frankly not too worried as:

according to "Excel 2002 VBA Programmer's Reference" by John Greene and Stephen Bullen on page 472: "While Excel recognises English names for command bars themselves, it does not recognise English names for the controls placed on them."
So using "Worksheet Menu Bar" should not be an issue (and the non-multilingual version I already tested confirmed this).
The routine in "Professional Excel Development" places custom bars at the end of all controls if it can not find the "Window" control (not "Help" as I mistakenly noted in my post). I will though make a mental note to make a slight amendment to the original code to use the control ID instead of the Caption.I do not think the reference you made to the KB entry actually solves my current issue (as you already indicated) but many thanks for responding!

[BenD]

I have had problems with menu items not being removed when I am trying to update them. My solution was to put the code in a separate procedure, and call that procedure as a delayed action using Ontime.

Hi xld,

You saved my day (and the last strands of hair I still have....)!!
Although the behaviour I see does not make sense, I would never have come to the conclusion that using a delay might help me further!
So yes, your solution works!

For archiving purposes I post the change of code below. This because delaying the re-creation of the menu created a different problem which I needed to resolve: The user selects a different language from a dropdown control in the menu, so I had to set the new language within that control after the menu got re-created. As there is a delay before the new menu appears, this menu settings needs to be handled after that delay. So another use for 'OnTime'. However, it appears that 'Application.Ontime' does not work from within a class module....

After some searching on the Internet I found a reference from the-ever-so-helpful Chris Pearson (see link (http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/thread/d8c3c6dd88fa7100/4fea1e864729ac20?hl=en&lnk=st&q=excel+call+a+procedure+in+class+module+using+OnTime#4fea1e864729ac20)). I needed a workaround to call a class method (delayed) from within a standard module. The code can be found below:

Part of code in class module:

' Translate the menu:
If Not bTranslateMenu Then Err.Raise glHANDLED_ERROR
' Create a new menu (that uses the new language labels):
' Build the custom commandbars specified in the wksCommandBars table:
' Note: For reasons unknown, the re-creation of the custom menu
' needs to be delayed a bit.
Application.OnTime Now + TimeValue("00:00:01"), "bBuildCommandBars"
'If Not bBuildCommandBars() Then Err.Raise glHANDLED_ERROR ' This does not work unfortunately, hence the delay.

' Set the current language in language Dropdown control:
If Not bDelaySetLanguageInMenu() Then Err.Raise glHANDLED_ERROR


Code in standard module:

Public sClassMethodToRun As String

' PROCEDURE : bDelaySetLanguageInMenu
' Date : 13/01/2008
' Author : Bennie Douma
' Description : Executes a class method (after a delay) from within a
' standard module.
'
' Notes : The class method should called after some delay. Normally this is
' done using 'Application.OnTime'. However it appears this does not
' function in a class module, OnTime should be called in a standard
' module. This subroutine is therefore a workaround.
' You can use 'CallByName' to call a particular method of a specified
' instance of an class object. The actual activity takes place in
' the private "ExecuteClassMethod" subroutine.
' Explained by Chris Pearson.
'
' Date Action
'-------------------------------------------------------------------------------
' 13/01/2008 Initial version
'
Public Function bDelaySetLanguageInMenu() As Boolean
Dim dDelay As Double
Dim bReturn As Boolean
Const sSOURCE As String = "bDelaySetLanguageInMenu()"
On Error GoTo ErrorHandler
bReturn = True
' The name of the class method to execute:
sClassMethodToRun = "bSetMenuLanguageSelection"
' Delay time before method is executed
dDelay = Now + TimeSerial(0, 0, 2)
Application.OnTime dDelay, "ExecuteClassMethod", , True

ErrorExit:
bDelaySetLanguageInMenu = bReturn
Exit Function
ErrorHandler:
If Err.Number <> glHANDLED_ERROR Then Err.Description = Err.Description & " (" & sSOURCE & ")"
bReturn = False
If bCentralErrorHandler(msMODULE, sSOURCE) Then
Stop
Resume
Else
Resume ErrorExit
End If

End Function

''' **************************************************************************
''' Module Private Procedures Follow
''' **************************************************************************
'-------------------------------------------------------------------------------
' PROCEDURE : ExecuteClassMethod
' Date : 13/01/2008
' Author : Bennie Douma
' Description : Executes a class method from the 'CTranslate' class.
'
' Notes : See notes at "DelaySetLanguageInMenu".
'
' Date Action
'-------------------------------------------------------------------------------
' 13/01/2008 Initial version
'
Private Sub ExecuteClassMethod()
CallByName mclsTranslate, sClassMethodToRun, VbMethod
End Sub



xld, you are a life saver! Many thanks!

P.S.:
I looked for this but can not find a reference. How do you mark a post as being solved? Should I edit my initial post and change the title?

[Bob Phillips]

There is a dropdown called Thread Tools at the head of the first post in the thread. What you want is in there.

[Bob Phillips]

You might even try a delay of 0, TimeSerial(0,0,0). I am not absolutely sure, but I think that was what I used. It is not the delay that is important AIR, but the handing the action off.

[BenD]

You are right, it even works with a zero delay on the rebuild of the menu. Setting the dropdown does need at least a second to work.

Again, many thanks indeed!