Spellchecking One Cell

When you apply the CheckSpelling method to a one-cell range, Excel continues to check the rest of the sheet and prompts you to continue spellchecking from the beginning of the sheet. You can use Application.DisplayAlerts = False to remove the prompt, but it still checks more than just that cell.

Excel range with two misspelled words

Sub CheckSpell()
    ‘Checking one cell will result in a prompt
   Range(“C3”).CheckSpelling
End Sub

message box to spellcheck from beginning

Tom Ogilvy noted in a recent newsgroup post that extending the range to more than one cell solves both problems.

Sub CheckNoMsg()
    ‘this eliminates the prompt, but still checks D4
   Application.DisplayAlerts = False
    Range(“C3”).CheckSpelling
    Application.DisplayAlerts = True
   
    ‘This checks c3 only – kind of
   Union(Range(“c3”), Range(“iv65536”)).CheckSpelling
End Sub

Combining, via Union, the range in question with a cell that we know to be empty (IV65536 in this case) limits the spellcheck to one cell – actually two, but who’s counting.

Posted in Uncategorized

15 thoughts on “Spellchecking One Cell

  1. Clicked post too early.

    Excel’s Union operator (the comma in US versions) will show duplicates.
    The VBA Union function has different logic – it tries to eliminate duplicates.

  2. If you don’t preprocess the union using the Union function, you don’t have to extend the checked range to another cell. For instance, using the example above, the following will also only check C3 (also without the continuation dialog):

    Range(“C3,C3?).CheckSpelling

    Why the difference? I guess it’s because the Union function returns a range representing the unique set of all cells it is given. So, given two ranges representing the same cell, it returns a one cell range:

    ?Union(Range(“C3?), Range(“C3?)).Cells.Count
    1

    However, an address-based union always returns all the cells it is given (duplicates are eliminated when processed):

    ?Range(“C3,C3?).Cells.Count
    2

    Apparently the SpellCheck method uses some crude cell count check to determine whether to continue checking the remainder of the document.

  3. I use Excel 2000 and use cells to store very long text strings. Although the spell checker picks up misspellings, it cannot correct them in cells containing more than around 1000 characters. This is one of a number of bugs (or nonworking features) which I have found when Excel is faced with cells containing very long text strings.

  4. I’m new to excel/vba programming, but am working on a small project that needs to work in both mac & pc versions of excel (I will insist that either version not be ancient – Excel 2000 or greater for the PC). I was surprised to see that this kind of workaround was needed on the PC-side to spellcheck a cell. This command “ActiveCell.Offset(0, 5).CheckSpelling” works fine on the Mac side, which is where I’m working doing initial development. Does MS fix stuff like this?

    Thanks,
    Tom

  5. Range(“C3,C3?).CheckSpelling doesn’t seem to work on merged cells. For instance if I merge cells C3 and C4, its name will be C3. When I try using

    Range(“C3,C3?).CheckSpelling

    Excel doesn’t spell check anything. Any ideas?

    Currently, I’m working around it by copying the contents of C3 (merged cell) into D4 (non-merged cell) and using

    Range(“D4,D4?).CheckSpelling

    That seems to do the trick. Is there a way I can avoid that?

  6. KD, this seems to work, but *stay away from merged cells* !!!

    Range(Range(“C3?).MergeArea.Address & “,” & Range(“C3?).MergeArea.Address).CheckSpelling

  7. Can someone tell me how to use .checkspelling and not launch the spell check dialog? If I have a word or a misspelled word in a cell, I want to get back a boolean that is true if the contents pass a spell check (words found in dictionary) and false if they don’t. I can dismiss the dialog with sendkeys but sendkeys is a last resort. Thanks.

  8. Jay,
    Did you ever find an answer to your question? i have the same question…

    I need to check a cell, return true if spelled correctly, false if not

  9. The routine below will check the contents of the selected cell and return either TRUE or FALSE in the adjacent cell.

    I tried to write it as a function, but it always returned FALSE. I don’t know why.

    Sub SpellChecksub()
    Dim CheckWord As String
    CheckWord = Selection.Value
    Selection.Offset(0, 1) = Application.CheckSpelling(CheckWord)
    End Sub

    Here’s the function:

    Function SpellCheck(CheckRange As Range) As Boolean
    Dim CheckWord As String
    CheckWord = CheckRange(1, 1).Value
    SpellCheck = Application.CheckSpelling(CheckWord)
    End Function

    Maybe someone can tell me why it doesn’t work?

  10. An “old” trick is to select the cell twice (select, then press the Ctrl key and click on the cell). Then, the spell checker will only operate on that cell.

  11. Jay, Doug,
    I guess this was solved in the comments of this post. See the the third post from Robert, the trick is to use a new Excel application object.. Yes, slow for one cell, but for many cells this works fine (according to my testing in 2003 at least).

  12. Here’s my complete solution:

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Locked Then Exit Sub
    If Target.Text vbNullString Then
    ‘*********************************************************************************************************
    ‘EXCEL SINGLE-CELL SPELL CHECK BUG WORKAROUND – BUG WORKAROUND – BUG WORKAROUND – BUG WORKAROUND – BUG

    ‘Excel has an annoying bug in which, if the CheckSpelling method is called for a single cell or merged
    ‘range, it continues spell-checking the rest of the sheet. The cleanest workaround is to specify multiple
    ‘cells (which suppresses the automatic continuation functionality) but with two cells that are actually
    ‘the same cell. Note that the specification must be a union of two cells (using the “,” operator), not a
    ‘multi-cell range. Also, any possible merged-area must be explicitly handled.

    Dim FullTargetAddr As String

    Application.EnableEvents = False ‘Disable events in case of multiple spelling errors, in which case
    ‘this event handler would be re-triggered on each spelling-fix event.
    ‘(But no need to save its state because this is an event handler)

    FullTargetAddr = Target.Resize(1, 1).MergeArea.Address ‘If merged, must use the full merged-range address,
    ‘not just its first cell!
    Range(FullTargetAddr & “, ” & FullTargetAddr).CheckSpelling

    Application.EnableEvents = True

    ‘END WORKAROUND – END WORKAROUND – END WORKAROUND – END WORKAROUND – END WORKAROUND – END WORKAROUND – END
    ‘*********************************************************************************************************
    End If
    End Sub


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *