BenD
01-13-2008, 07:46 AM
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
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