5

In MS Excel, is it possible to set the content language of cells e.g. per column? The purpose would be, among other things, to make proper spelling checks when the content is multilingual (e.g., a dictionary), like we can do in MS Word.

2 Answers 2

3

Here's a quick-and-dirty macro that you can hopefully adapt to your needs. As it is now, it will run a spell check in American English on Column A, and then run a spell check in Mexican Spanish in Column B. You can find the codes for supported languages here.

Sub multilanguageSC()
Dim rngEng As Range, rngSpa As Range

'Set ranges to check for each language.
Set rngEng = ActiveSheet.Range("A:A")
Set rngSpa = ActiveSheet.Range("B:B")

'Set spelling dictionary language to English (US).
Application.SpellingOptions.DictLang = 1033
'Check spelling for designated English range.
rngEng.CheckSpelling

'Set spelling dictionary language to Spanish(Mexico).
Application.SpellingOptions.DictLang = 2058
'Check spelling for designated Spanish range.
rngSpa.CheckSpelling

'Set spelling dictionary back to default setting.
Application.SpellingOptions.DictLang = Application.LanguageSettings.LanguageID(msoLanguageIDUI)

End Sub
5
  • 1
    nice answer! well done! Jun 15, 2012 at 2:56
  • That's very clever. +1 Jun 15, 2012 at 5:08
  • Nice indeed, it solves the spelling checking problem. It does not actually set the language of the cells, so that e.g. clicking on a cell could change the keyboard settings to match the language, which was part of the motivation behind the question. Jun 15, 2012 at 5:27
  • @JukkaK.Korpela I see. From what I gathered in my brief research, the language settings of Excel are read-only (i.e., they can't be changed through VBA) with the exception of spelling dictionaries. There may be some way using VBscript to change the keyboard settings. If so, the way to go would be to have that code triggered by the SelectionChange event.
    – Excellll
    Jun 15, 2012 at 14:27
  • See How do I add VBA in MS Office?  for general information on using VBA. Sep 1, 2020 at 9:45
0

Yes, it can be done. Here is an example for Japanese and English input. (You can make different settings for the type of Japanese input you want to allow.)

I use the Input Rules on the Data tab. (I have to set the "Options", "Language", "Editing Language" to "Japanese")

Just go to the "Data" tab and then select "Conditional Rules" from the "Data Tools" group. There will now be a new tab on the right called "Japanese Input". Set one column to "On", the other to "Off", and you're ready to go.

Looks like they can be done with VB too. Not sure about the cell ranges though.

Sub Cond()
'
' Cond Macro
'

'
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .IMEMode = xlIMEModeOn
        .ShowInput = True
        .ShowError = True
    End With
End Sub

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .