Spell check unlocked cells with highlighting

[sparky2205]

Hi folks,
this is one of those ones where there is a lot of chatter out there about it but I haven't been able to get a fix for my issue.
My issue:
I want a macro that performs a spell check on the unlocked cells only on a protected sheet while highlighting the cell where the error occurs.
What I have so far is:

Sub SpellCheckUnlockedCells()
 Dim WorkRange As Range
 Dim FoundCells As Range
 Dim Cell As Range
 Set WorkRange = ActiveSheet.UsedRange
 
    ActiveSheet.Unprotect ("")
    For Each Cell In WorkRange
       If Cell.Locked = False Then
           If FoundCells Is Nothing Then
           Set FoundCells = Cell
           Else
           Set FoundCells = Union(FoundCells, Cell)
           End If
       End If
    Next Cell
   
    If FoundCells Is Nothing Then
           MsgBox "All cells are locked."
     Exit Sub
    Else

       Application.ScreenUpdating = True
       Application.EnableEvents = True
       
       FoundCells.Application.CommandBars("Tools").Controls("Spelling...").Execute
       
       If WorkRange Is Nothing Then
        Exit Sub
       End If
       
       MsgBox "Spell checking complete."
    End If
 End Sub

This does perform the spell check but on all cells not just unlocked cells.
I previously used the activesheet.checkspelling method which does limit the spell check to locked cells but does not highlight the cell where the error is.
Is this something we just have to live with? i.e. we can limit the spell check to unlocked cells only or we can have the cell where the error occurs highlighted but not both at the same time?
Any comments/help appreciated.

 

[Jaafar Tribak]

Hi Jaafar,
here is the link: TinyUpload.com - best file hosting solution, with no limits, totaly free
I get the error at Cell.Value = Split(Cell.ID, "*")(0) in errHandler:

Sorry but the workbook has too many sheets and I don't know which is the one I need to test ... Furthermore, the password "Test" doesn't seem to be correct plus I don't see the button that runs the SpellCheckUnlockedCells macro.

It would be much easier for me If you could make it simpler, remove all unnecessary worksheets, leave all redundant stuff out and only keep one worksheet with a button that runs the spellcheck macro to illustrate the issue you are having .

Regards.

 

[sparky2205]

Good morning Jaafar,
my apologies, the result of doing the upload just before leaving the office.
The new file has two sheets, one is a source data for drop downs so I left it in case deleting it would cause issues.
The ECR sheet is the one of interest. I have put a button on it for the Spell Check. I was using ALT + F8 to run the macro.
The password Test will now work.
I have noticed something else this morning. When I run the macro now it appears to be checking locked cells, which I didn't think it did before.
In particular look at row 50 where I have purposely spelled validation as vallidation.
Also row 52 where preceding has been spelled as preceeding. The macro appears to be checking both these cells even though they are locked cells.
The other error is also still occurring, i.e. "Subscript out of range" as described previously.

Link: TinyUpload.com - best file hosting solution, with no limits, totaly free

 

[Jaafar Tribak]

Ok- I'll take a look at it later on and see what happens .
Thanks.

 

[Jaafar Tribak]

It is rather daunting to debug.

Now there is one thing I dind't think about:
if you use Application.CommandBars("Tools").Controls("Spelling...").Execute , the spellchecker highlights all cells including those that are locked BUT if you make sure that the text inside the locked cells is spelt correctly in each locked cell then the spellchecker won't highlight them afterwards ... Since the user won't be able to edit the locked cells , these cells will reamain well-spelt and therfore won't be highighted.

I hope I explained the idea clearly.

 

[sparky2205]

Yes, I do understand what you are saying. But some of the locked cells will contain acronyms that won't be recognised by the spell checker and will therefore always be highlighted.
What I don't understand is when I initially checked your code yesterday, albeit on a very small dataset (see #6), the locked cells were not selected at all. I just checked this again and this remains the case. But in my larger spreadsheet they are selected. There must be something in the structure of the larger spreadsheet that is causing this.
As you say it is very difficult to debug. I might have to gradually build up a spreadsheet to see if I can isolate the problem.
Did you have any luck tracing why I am getting the Subscript out of range error? Or did you even see this issue? Maybe it's all related?

 

[Jaafar Tribak]

I see - Well I am afraid I now realise that the code I posted will only work if the cells have one word each ... If the cells have more than one word (such as in your worksheet) the adding of a number trick to the cells won't work .

This is challenging. The only other thing that should work (Albeit ugly) is to store the values of the locked cells then temporarly clear their contents , perform the spellcheck and finally restore the locked cells values. If you think this alernative is ok then we should be able to easily adapt the code.

Let's see if someone see this thread and can find a better workaround.

 

[sparky2205]

Good morning Jaafar,
at least I know now why the code doesn't work on the larger spreadsheet. That was baffling me.
I don't anticipate too many other responses to this post as it's been posted for a while.
Thanks very much for your efforts anyway and I did sort of hijack you onto this post in the first place.
Thanks for sorting my other issue, selecting the sheet names from a list rather than having to type in the names. This will be very useful.

Thanks again for your help and have a good day.

 

[Jaafar Tribak]

Good morning Jaafar,
at least I know now why the code doesn't work on the larger spreadsheet. That was baffling me.
I don't anticipate too many other responses to this post as it's been posted for a while.
Thanks very much for your efforts anyway and I did sort of hijack you onto this post in the first place.
Thanks for sorting my other issue, selecting the sheet names from a list rather than having to type in the names. This will be very useful.

Thanks again for your help and have a good day.

You are welcome.