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.
You could also do it this way:
Range(“C3, C3?).CheckSpelling
Why does this work?
Msgbox Range(“c3, c3?).Cells.Count
Cheers,
Rob
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.
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.
Wow – that’s what I get for re-reading my comment before submitting it! Those were some fast responses. Sorry to be repetitive! :)
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.
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
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?
KD, this seems to work, but *stay away from merged cells* !!!
Range(Range(“C3?).MergeArea.Address & “,” & Range(“C3?).MergeArea.Address).CheckSpelling
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.
I need to check a cell, return true if spelled correctly, false if not. Any ideas?
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
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?
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.
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).
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