Language changing Macro

[andrewb90]

Hello,

I have a user who may find my workbook easier to use if it is in his home language. Is it possible to have a macro button that can switch the language of the page and/or the sheet into my specified language (in this case Chinese)?
Also I have a number of other macro buttons with text on them on many of my sheets if a macro button is possible, can we make the text on the buttons change? If not, I can adjust the text on the buttons.


Many Thanks,

Andrew

 

[Cindy Ellis]

I was hoping someone would come with the perfect solution for you...but it looks like it's been a while with no response, so I'll chip in with something that's not particularly elegant, but could do the job, depending on the format of your workbook. This assumes that you're not looking for a solution that actually does the translation for you, but a solution that swaps the view from pre-entered English to pre-entered Chinese or other language.
For a simplistic example, let's say all of the text to be translated is in Rows 1-3 (title and header rows) and Column A (Record identifiers, for instance). If you insert 3 rows (4-6) with the translation of rows 1-3, and insert a new column B with with translation of column A, you could use a macro button to hide and show the rows & column, essentially swapping between the 2 using the macro:

Sub Swap_Language()
'Hide English - show Chinese
If Rows("1:3").EntireRow.Hidden = False Then
    Rows("1:3").EntireRow.Hidden = True
    Columns("A:A").EntireColumn.Hidden = True
    Rows("4:6").EntireRow.Hidden = False
    Columns("B:B").EntireColumn.Hidden = False
Else  'Hide Chinese - show English
    Rows("1:3").EntireRow.Hidden = False
    Columns("A:A").EntireColumn.Hidden = False
    Rows("4:6").EntireRow.Hidden = True
    Columns("B:B").EntireColumn.Hidden = True
End If
 
End Sub

I labeled the button "English <--> 中文" using Arial Unicode as the font so that Chinese characters would display OK on the button face.

Obviously, the specific columns and rows would have to be modified for your application, and it may not work for you at all, depending on the layout of your workbook. Hopefully it will help you get to a workable solution.

Cindy

 

[andrewb90]

The layout of my sheets is a bit complex, so creating a identical copy to have hidden and unhidden would be very complex to do and to make work.

I appreciate the response, but I think I need to see if there is a way to have the language change to Chinese through the use of a macro. I am not sure if this is even possible, but if anybody has any thoughts on if it can or cannot be done, please let me know.

Thanks,

Andrew

 

[Cindy Ellis]

Unless you're expecting "on the fly" translation, you will need to have all of the text translated and stored somewhere. So, another approach to consider would be to have one hidden worksheet with the English text in Column A, and the Chinese translation in Column B. Somewhere on the main sheet, you would have a "data validation" cell with a list, containing "English" and "中文" as the choices. The formula in each text cell then refers to the selection, with a pointer to the text for that cell:

=IF(B1="English",HiddenSheet!A2,HiddenSheet!B2)

I have some other ideas if this one isn't what you need.
Cindy

 

[andrewb90]

I am really looking for some sort of function that could translate a page "on the fly" Its a lot of text and cell formatting that I would lose by trying to duplicate everything in Chinese.

 

[Cindy Ellis]

I've just been involved in a translation project involving 8 languages and around 1500 text strings (I was the customer, not the translator!). Based on that, my opinion is that any on-the-fly translation (such as google translate) will give a rough idea of the translation but will not provide a professional result. That said, I don't know how to invoke an external translation program from within Excel.
Sorry I couldn't be of more help.
Cindy