2

I’m trying to create a macro that will only spellcheck specific cells. I have succeeded in spellchecking the cells, but for some reason the spellcheck wizard keeps running afterwards and tries to check any text boxes on my spreadsheet.

Below is the code:

Range(“C8”).Select
Selection.CheckSpelling SpellLang:=1034

Updating code with suggestions, however the code is still spellchecking text boxes:

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

Const dummyCell = "Z999" 'address of an empty cell
Dim cellsToCheck As Range

Set cellsToCheck = ws.Range("C8")

Union(Range(dummyCell), cellsToCheck).CheckSpelling

I cycled through and it is spellchecking the text boxes before the actual range specified in "CellsToCheck".

rbpicanco
  • 21
  • 2

1 Answers1

3

Programmatically check spelling of a single cell without the dialog box

If you want to programmatically check the spelling on a single cell, and you don't want the Spelling dialog box to display you can use the CheckSpelling method as it applies to the Application object.

Sub checkSpelling_NoDialog()

    Dim correctlySpelled As Boolean, textToCheck As String

    textToCheck = Range("A1")
    correctlySpelled = Application.checkSpelling(textToCheck)

    If Not correctlySpelled Then
        MsgBox "Incorrect Spelling of: " & textToCheck
    Else
        MsgBox "Correct Spelling of: " & textToCheck
    End If

End Sub

Programmatically check spelling of a single cell with the dialog box

If you do want the Spelling dialog box to display but only want to check one cell, you need to "trick Excel". Excel is designed to, if you've only selected one cell, assume you actually want to check the entire worksheet.

Sub checkSpelling_WithDialog()

    Const dummyCell = "Z999" 'address of an empty cell
    Dim cellsToCheck As Range

    Set cellsToCheck = Range("A1")

    Union(Range(dummyCell), cellsToCheck).checkSpelling

End Sub

More information on the CheckSpelling method is outlined here and here.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • Hi Ashlee, thanks for this. Unfortunately the workaround above still has the same error, it checks the specified cell and then checks the spelling on the text boxes. – rbpicanco Jul 10 '18 at 11:55
  • Very nice answer +1!. Only one detail I will add, as you may have noticed in ashleedawg 's code, she hasn't used a single `.Select` method. That often is for a very good reason, as it is prone to many errors / unexpected behaviour. I liked [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in particular. Feel like you should definitely read at least 1 or 2 of the answers there, @rbpicanco as it's definitely a habit that's worth unlearning – Samuel Hulla Jul 10 '18 at 11:56