Data Validation "Input Message"

[julie_nickb]

I am developing a bilingual software in Excel.
I would like to know whether it's possible to change the "Input Message" in Data Validation to display the message in the selected language.
i.e., let's say that variable "Language" is 1 for English, and 2 for French.
If I have two drop-down lists that are Named Ranges, can I select the one that is in the appropriate language?

Thank you.

 

[kweaver]

I think you could accomplish this with dependent drop-downs.
I created two columns of data in D and E; selected those (D2:E6) and made it a name range using the top row.
Then, the drop-down in B3 becomes dependent by using the List, Source where the Source is: =INDIRECT(B2).
You could put those two column references on another sheet.

Book4
	A	B	C	D	E
1
2	Language/Langue	Francais		English	Francais
3	Emplacement	Nord		North	Nord
4				South	Sud
5				East	Est
6				West	Ouest
Sheet1

Cell Formulas
Range	Formula
A3	A3	=IF(B2="English","Location","Emplacement")

 

[julie_nickb]

I think you could accomplish this with dependent drop-downs.
I created two columns of data in D and E; selected those (D2:E6) and made it a name range using the top row.
Then, the drop-down in B3 becomes dependent by using the List, Source where the Source is: =INDIRECT(B2).
You could put those two column references on another sheet.

Book4
	A	B	C	D	E
1
2	Language/Langue	Francais		English	Francais
3	Emplacement	Nord		North	Nord
4				South	Sud
5				East	Est
6				West	Ouest
Sheet1

Cell Formulas
Range	Formula
A3	A3	=IF(B2="English","Location","Emplacement")

Thank you so much for your reply.
However, in "Data Validation" there are three tabs: "Settings". "Input Message" and "Error Alert".
This solves the language of the validation drop-down list in "Settings", but not the language of the "Input Message" and "Error Message".
Could you help me with those?
Thanks again.

 

[kweaver]

I think this macro will do it for you based on my original layout. You can put a worksheet change event in the sheet to trigger this rather than having to run it every time.
I suspect there's a shorter macro that will do it...but one of the ace's here will have to suggest it.

Sub ChangeLanguage()
'replace "B3" with the cell you want to insert the drop down list
With Range("B3").Validation
.Delete
If Range("B2") = "English" Then
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Sheet1!D3:D6"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Select"
.ErrorTitle = ""
.InputMessage = "Select One"
.ErrorMessage = "Try again..."
.ShowInput = True
.ShowError = True
Else
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Sheet1!E3:E6"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Sélectionnez"
.ErrorTitle = ""
.InputMessage = "Sélectionnez-en un"
.ErrorMessage = "Réessayer..."
.ShowInput = True
.ShowError = True
End If
End With
End Sub

 

[julie_nickb]

I think this macro will do it for you based on my original layout. You can put a worksheet change event in the sheet to trigger this rather than having to run it every time.
I suspect there's a shorter macro that will do it...but one of the ace's here will have to suggest it.

Sub ChangeLanguage()
'replace "B3" with the cell you want to insert the drop down list
With Range("B3").Validation
.Delete
If Range("B2") = "English" Then
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Sheet1!D3:D6"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Select"
.ErrorTitle = ""
.InputMessage = "Select One"
.ErrorMessage = "Try again..."
.ShowInput = True
.ShowError = True
Else
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Sheet1!E3:E6"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Sélectionnez"
.ErrorTitle = ""
.InputMessage = "Sélectionnez-en un"
.ErrorMessage = "Réessayer..."
.ShowInput = True
.ShowError = True
End If
End With
End Sub

Thanks, I will try it

 

[julie_nickb]

I think this macro will do it for you based on my original layout. You can put a worksheet change event in the sheet to trigger this rather than having to run it every time.
I suspect there's a shorter macro that will do it...but one of the ace's here will have to suggest it.

Sub ChangeLanguage()
'replace "B3" with the cell you want to insert the drop down list
With Range("B3").Validation
.Delete
If Range("B2") = "English" Then
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Sheet1!D3:D6"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Select"
.ErrorTitle = ""
.InputMessage = "Select One"
.ErrorMessage = "Try again..."
.ShowInput = True
.ShowError = True
Else
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Sheet1!E3:E6"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Sélectionnez"
.ErrorTitle = ""
.InputMessage = "Sélectionnez-en un"
.ErrorMessage = "Réessayer..."
.ShowInput = True
.ShowError = True
End If
End With
End Sub

Thank you, Kweaver, it works!

 

[kweaver]

Thanks for the feedback. That was a fun one to do.