none
Highlight misspelled words while running spell check RRS feed

  • Question

  • Hello,

     

    I would appreciate a little help with a macro if what I want to do is possible.

    I have  a macro that unprotects each worksheet and allows for a spell check and then re-protects.  Is there simple code that would allow the spell check to highlight the misspelled word and then remove the highlight when corrected?

    I have seen some posts but most make it so complicated.

    My code is below:

    Public Sub SpellCheckPerfReview()

    Dim WS As Worksheet
          For Each WS In ActiveWorkbook.Worksheets
          WS.Select
          ' unprotect each worksheet to do the spell check
          ActiveSheet.Unprotect Password:="OurPassword"
          'Highlight each mispelled word
        
          Cells.CheckSpelling SpellLang:=1033
        ActiveSheet.Range("A1").Select
          ActiveSheet.Protect Password:="OurPassword", AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFormattingCells:=True

          Next WS
         'move the cursor to the Employee Information sheet and select A1
          Sheets("Employee Information").Select
           
            Range("A1").Select
          Application.ScreenUpdating = True

    End Sub


    Middleground

    Thursday, March 8, 2012 2:33 PM

Answers

  • Sure, the sub routine will not write the misspelled words to another Column, it will only set a text "Misspelled" in a Cell.

    This option you can disable in the code if you want.

    Make sure you have the whole subroutine in a seperate Module saved.

    Then to call the Sub, you do something like this in your Macro:

    Public Sub SpellCheckPerfReview()
     
    Dim WS As Worksheet
           For Each WS In ActiveWorkbook.Worksheets
           WS.Select
           ' unprotect each worksheet to do the spell check
           ActiveSheet.Unprotect Password:="OurPassword"
           'Highlight each mispelled word
          
    '      Cells.CheckSpelling SpellLang:=1033
    
            ' //// Call the Sub routine ////
            Call HighlightMisspelledWords 
    
        ActiveSheet.Range("A1").Select
           ActiveSheet.Protect Password:="OurPassword", AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFormattingCells:=True
     
          Next WS
          'move the cursor to the Employee Information sheet and select A1
           Sheets("Employee Information").Select
             
            Range("A1").Select
           Application.ScreenUpdating = True
     
    End Sub
    

    Hope this helps,

    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    • Marked as answer by Middleground Monday, March 12, 2012 4:36 PM
    Thursday, March 8, 2012 6:42 PM
    Moderator

All replies

  • This thread offers a solution to do exactly that, you can copy it as a seperate Sub routine in your Module and call it in your Macro.

    Or encorperate the whole routine in your Macro.

    http://visualbasic.ittoolbox.com/groups/technical-functional/visualbasic-l/excel-spell-checker-vba-code-3017915

    I tested it and it works very well.

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Thursday, March 8, 2012 5:38 PM
    Moderator
  • Daniel,

    Thanks for pointing me to this code.  It may be more than I need.  I definitely do not want to write the misspelled words to another column.  I tried to comment out the sections that appears to do that, but I keep getting error messages.  Also, I need to spellcheck all of the worksheets at one time and each is protected.  I know you said I could call the code from my procedure, but I am not sure how to do that.  I am knew at VBA.  More sage advice would be appreciated.  Thanks!


    Middleground

    Thursday, March 8, 2012 6:26 PM
  • Sure, the sub routine will not write the misspelled words to another Column, it will only set a text "Misspelled" in a Cell.

    This option you can disable in the code if you want.

    Make sure you have the whole subroutine in a seperate Module saved.

    Then to call the Sub, you do something like this in your Macro:

    Public Sub SpellCheckPerfReview()
     
    Dim WS As Worksheet
           For Each WS In ActiveWorkbook.Worksheets
           WS.Select
           ' unprotect each worksheet to do the spell check
           ActiveSheet.Unprotect Password:="OurPassword"
           'Highlight each mispelled word
          
    '      Cells.CheckSpelling SpellLang:=1033
    
            ' //// Call the Sub routine ////
            Call HighlightMisspelledWords 
    
        ActiveSheet.Range("A1").Select
           ActiveSheet.Protect Password:="OurPassword", AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFormattingCells:=True
     
          Next WS
          'move the cursor to the Employee Information sheet and select A1
           Sheets("Employee Information").Select
             
            Range("A1").Select
           Application.ScreenUpdating = True
     
    End Sub
    

    Hope this helps,

    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    • Marked as answer by Middleground Monday, March 12, 2012 4:36 PM
    Thursday, March 8, 2012 6:42 PM
    Moderator
  •  

    Daniel,

    Thanks for explaining.  I did as you said after commenting out the lines that show the misspelled words in another column.  I must misunderstand what this is supposed to do.  I assumed it highlights the misspelled word before it is corrected so the end-user can see which word is actually wrong in context. It goes through the spell check but does not highlight the words.  It also spell checks the used range instead of the unlocked cells.  Is there anyway to have it look at only unlocked cells?

    Thanks again.


    Middleground

    Thursday, March 8, 2012 9:19 PM