Check Spelling Method For Single Cell

Re: Check Spelling Method For Cell Only

That can be irritating... but I think I actually take sides with MS on this implementation decision.

By default, if the spellchecker detects one cell is selected, it assumes you want the whole sheet checked. Basically trying to account for silly users who are likely to forget and do a spell check with a single cell selected. In which case, they'd complain "I did run a spellcheck and Excel didn't find anything."

Since any range selection with a cell count greater than 1 would pass the test, I'd just pick a dummy cell that I know will always be empty and include it in the spellcheck range.

Sub spellchecker()


    Dim DumCell As Range, CheckCell As Range
    Set DumCell = Range("A1")
    
    Set CheckCell = Range("B7")
    Union(DumCell, CheckCell).CheckSpelling
    
End Sub

Re: Check Spelling Method For Single Cell

Hmm, that seems a very long winded way of doing it.
Why not define one named range for all the cells to be checked on each sheet. eg 3 sheets, 3 names, then run the spell check three times.
Then your code would be about 5 lines, and no union function reqd.

Also, you've used relative references (no $ signs) which may make the behaviour a bit erratic

Re: Check Spelling Method For Single Cell

Quote from BruceMutton

Hmm, that seems a very long winded way of doing it.
Why not define one named range for all the cells to be checked on each sheet. eg 3 sheets, 3 names, then run the spell check three times.
Then your code would be about 5 lines, and no union function reqd.

Also, you've used relative references (no $ signs) which may make the behaviour a bit erratic

The purpose of the union in the first place was a workaround to the spellcheck entire sheet if the spellcheck range is a single cell. So no, you'll still need the union workaround if checking a single cell on a sheet.

Re: Check Spelling Method For Single Cell

Quote from kpfunf

Bruce,
Yea I knew the way I wrote the code was long...could you give me an example of what you're talking about?

A more efficient way to do that would be...

Sub test()


    With Worksheets("Section IV,V&VI-MUST COMPLETE")
        .Range("B7,B12").CheckSpelling
    End With
    With Worksheets("Section II&III- MUST COMPLETE")
        .Range("B53,B55,B59,B61").CheckSpelling
    End With
    With Worksheets("Section II&III- MUST COMPLETE")
        .Range("B20,B22,B26,B28").CheckSpelling
    End With
    
End Sub

Where if the situation should occur that you have a single cell to spellcheck, you just include a reference to an empty dummy cell.

However, I'm not crazy about the hardcoded range references. The suggestion to assign workbook defined names to the non-contiguous ranges of each sheet does make more sense. ...and that way you don't have to worry about updating code if you need to insert rows/columns cuz the defined names would update automatically.

You'd just Ctrl-Click on each cell of a given sheet to create the non-cont spellcheck range and then on the main menu select: Insert|Name|Define and choose an appropriate name "Spellcheck1". Repeat for each sheet and your code simplifies to...

Sub test()


    Range("SpellCheck1").CheckSpelling
    Range("SpellCheck2").CheckSpelling
    Range("SpellCheck3").CheckSpelling
    
End Sub

...and you don't have to worry about updating the range refs if rows/cols are inserted/deleted.

You could avoid the union issue completely as long as the defined names all contain more than a single cell. If you wanted to be extra safe, I s'pose you could still include a dummy cell union.