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.
Sub CheckSpell()
'Checking one cell will result in a prompt
Range("C3").CheckSpelling
End Sub
'Checking one cell will result in a prompt
Range("C3").CheckSpelling
End Sub
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
'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.
Rob van Gelder:
You could also do it this way:
Range("C3, C3").CheckSpelling
Why does this work?
Msgbox Range("c3, c3").Cells.Count
Cheers,
31 March 2005, 2:04 pmRob
Rob van Gelder:
Clicked post too early.
Excel's Union operator (the comma in US versions) will show duplicates.
31 March 2005, 2:07 pmThe VBA Union function has different logic - it tries to eliminate duplicates.
Eric W. Bachtal:
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.
31 March 2005, 2:23 pmEric W. Bachtal:
Wow - that's what I get for re-reading my comment before submitting it! Those were some fast responses. Sorry to be repetitive!
31 March 2005, 2:26 pmPeter Grebenik:
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.
31 March 2005, 4:21 pmTom Olson:
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,
8 May 2005, 12:04 pmTom
KD:
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?
26 May 2005, 10:05 amJuan Pablo González:
KD, this seems to work, but *stay away from merged cells* !!!
Range(Range("C3").MergeArea.Address & "," & Range("C3").MergeArea.Address).CheckSpelling
26 May 2005, 10:55 amKevin Carhart:
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.
1 July 2005, 4:44 pmjay:
I need to check a cell, return true if spelled correctly, false if not. Any ideas?
28 March 2007, 12:07 amSteve:
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
6 November 2007, 4:15 pmDoug Jenkins:
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?
7 November 2007, 9:45 pmDavid Hager:
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.
9 November 2007, 6:26 pmJon Mercado:
tak9ly5sp5srn8n9
12 November 2008, 8:09 pm