VBA Sub switchLanguage()

[DDelainy]

Hello there,
I'm creating a bi-lingual template that need to have functionality to switch language En/Fr in certain cells.
1. I created a separate sheet ("EN-FR") with English wording in column A and French working in column B
2. On the main sheet I used OFFSET function to indicate English/French wording, for example =OFFSET('EN-FR'!$A$2,0,'EN-FR'!$B$2)
3. I added an ActiveX button that will be called Français or English depending on which language you're switching to.
4. Can you please help me figuring out the code? I assume I need to call the button btnLang and call switchLanguage sub. The button caption needs to switch from Français to English when we translate document into French and vice versa. The sheet is obviously protected, with "Password1"

Private Sub btnLang_Click()
Call switchLanguage

Sub switchLanguage()


Thanks!

 

[Micron]

Maybe something like this

Private Sub btnLang_Click()
  switchLanguage (btnLang.Caption)
End Sub

Sub switchLanguage(strCaption As String)

Select Case strCaption
     Case "French"
          'do stuff for French?
          btnLang.Caption = "English"
     Case "English"
          'do stuff for English?
          btnLang.Caption = "French"
End Select

End Sub

I might set a range object based on that and do something to it, or maybe alter the sheet formula but I have no idea what that would involve.

 

[DDelainy]

Thanks for that @Micron I tried playing with it but couldn't make it work. Would you have any other suggestions?

 

[Micron]

Yes - post what you tried. "Doesn't work" doesn't help anyone to help you. What was your result?
Please post code within code tags (vba button on posting toolbar) to maintain indentation and readability.

 

[DDelainy]

Hi @Micron I went into another direction. I used IF function in each cell with reference to cell "B1" that toggles between value "0" and "1".

Private Sub btnLang_Click()
ActiveSheet.Unprotect "Password1"
    With Range("B1")
    .Value = IIf(.Value = 1, 0, 1)
  
  'toggle button captions
  btnLang.Caption = IIf(Range("B1").Value = 0, "Francais", "English")
    End With
ActiveSheet.Protect "Password1"
End Sub

I'm testing it now (as I have a few drop-downs and another value-dependent VBAs, fingers crossed it works well for me. Thanks!