Form buton language option with VBA

[Jenlasan]

Hi all,

happy holidays to everyone!!!

I am linking you my file,

Basically I would like to have two butons at the top of the first page that say Français and English. Now whichever one is picked displays the language selected on the ENTIRE page. Except of course for the fill in areas. I am currently translating from french to english so you will have the french version for now. I want to know if it's something that can be easily done since it would mean giving one file instead of two to people.

I would greatly appreciate all your input.

https://drive.google.com/file/d/0B41...ew?usp=sharing

Thank you,Test 1.xlsm

[Andy Pope]

It does not require VBA. You can do it with formula and a lookup table.

I added a new sheet called TEXT which would contain the english/french text.
The option button choice is store on that sheet in the named range USE_COUNTRY
The table of text is named DISPLAY_TEXT

On Summary sheet I have added 4 cells worth of lookup names

=INDEX(DISPLAY_TEXT,1,USE_COUNTRY)

You simply change the 1 to the relevant row to lookup.

If the design of your sheets is complete and unlikely to change you could use a vlookup formula instead of a fix row number. You could lookup the sheet and cell location in order to determine which text to use. You would need to add 1 to the USE_COUNTRY value if you use vlookup

[Jenlasan]

Hi Andy thanks for the reply.

I was mainly looking for a vba because no one actually messes with VBA programming around here except me. Formulas on the other hand they will just do as they please, more often than not write all over them. Sadly when you have to constantly section lock it gets irritating.

I ended up getting a VBA solution. (In french but anyone can get the gist.

Dans un module, il convient de créer :
- Une variable publique :

Public Langue As String


- deux macros à relier aux cases d'option

Sub Casdoption5_Cliquer()
Langue = "Fr"
Rempli_Feuil_Summary
End Sub

Sub Casdoption2_Cliquer()
Langue = "En"
Rempli_Feuil_Summary
End Sub


- Une macro de "remplissage" des cellules concernées

Sub Rempli_Feuil_Summary()
Select Case Langue
Case "Fr"
Range("B2") = "Nom de l'unité:"
'Etc...
Case "En"
Range("B2") = "Unit name:"
'Etc...
Case Else
Debug.Print "ceci ne devrait pas se produire."
End Select
End Sub


Dans le module ThisWorkbook, initialiser la variable Langue par défaut à l'ouverture du classeur :

Private Sub Workbook_Open()
Langue = "Fr"
Rempli_Feuil_Summary
End Sub

Again thank you for the help!
Happy holiday!

[Andy Pope]

Even if you want to use VBA I would suggest storing the text in a table so it can be easily extended and updated.

Also the code can then be kept to a minimum as you only need to loop through the table of text