Check Spelling Method For Single Cell

  • When I use the following code, the spell check still checks the entire sheet. How do I have the spell checker on check a specific cell/range of cells?

    Code
    Sub spellchecker()
    Range("B7").CheckSpelling
    End Sub
  • 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.


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

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Check Spelling Method For Single Cell


    This code debugs on the Union part. Can mutli-worksheet ranges not be unioned?

  • 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


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

  • 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.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Check Spelling Method For Single Cell


    OK. Aaron, thanks for the initial code. What I decided was I would use a list of cells that need to be checked. So, how do I check these specific cells? I tried that with the above code, but that didn't work.

  • 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...


    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...


    Code
    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.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!