• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Spell checking locked worksheets and identifying cell with error.

S

skippy0070

New Member
Hello,

I am not sure if this is in the right place but I am hoping someone may be able to help me solve a coding problem. The following code is for spell checking locked worksheets unlocked cells only and it works fine but does not identify the cell with the spelling error;


Code: 
Sub SelectUnlockedCells_Spellcheck()


ActiveSheet.Unprotect Password:=""

  Dim WorkRange As Range
  Dim FoundCells As Range
  Dim Cell As Range
  Set WorkRange = ActiveSheet.UsedRange
  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."
  Else
  FoundCells.CheckSpelling CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, AlwaysSuggest:=True, SpellLang:=3081
  End If
ActiveSheet.Protect Password:=""


End Sub

I am trying to insert the following line which identifies the cell with the error using the command bar tools but I am not very good with the VBA coding and just can’t figure it out;

Code: 
CommandBars("Tools").Controls("Spelling...").Execute

I am trying it in place of;

Code: 
FoundCells.CheckSpelling CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, AlwaysSuggest:=True, SpellLang:=3081

It works okay but then I lose the ability to check only unlocked cells. Any ideas are greatly appreciated.
 
Last edited by a moderator:
You need ScreenUpdating enabled. Code should be something like.
Code: 
Application.ScreenUpdating = True
Application.CommandBars("Tools").Controls("Spelling...").Execute
 
Hi Chihiro, I gave that a try and it still scans the entire worksheet, not just the unlocked cells. I only want unlocked cells spell checked. This code works but does not identify the cell with the error;

Code: 
FoundCells.CheckSpelling CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, AlwaysSuggest:=True, SpellLang:=3081

Your suggestion overrides the FoundCells portion of code.

I would like to use something like this but cannot make it work for the life of me;

Code: 
FoundCells.CommandBars("Tools").Controls("Spelling...").Execute

Or is there some way to use the;

Code: 
FoundCells.CheckSpelling CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, AlwaysSuggest:=True, SpellLang:=3081

And make it identify the cell with the error?
 
Tack this on before "ActiveSheet.Protect"
Code: 
Dim r As Range
For Each r In Selection
If Application.CheckSpelling(r.Value) Then
Else: r.Interior.ColorIndex = 3
End If
Next r
 
Thanks Chihiro but that will not work as it still scans the entire worksheet not just the unprotected cells. My other issue with it is that it leaves the cell red even if you ignore the error and some names are not going to be accepted.

I would rather it temporarily highlight the cell until it moves to the next cell or is ignored and only spell check unlocked cells.
 
Hi skippy0070,

To clarify, the code above should be combined with your original code.
That will only highlight cells that are unlocked and check only unlocked cells.

Let me see if I can find ways to do 2nd part to remove highlight after Ignore or change has been applied.

Regards,
 
Final code should look like this.

Instead of highlighting the cell with colour, it will have selection on the cell.
upload_2015-7-30_15-11-16.png
Code has been modified to check each cell individually and will loop until all r in FoundCells are checked.

Code: 
Sub SelectUnlockedCells_Spellcheck()

ActiveSheet.Unprotect Password:=""

Dim WorkRange As Range
Dim FoundCells As Range
Dim Cell As Range
Set WorkRange = ActiveSheet.UsedRange

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

Dim r As Range
If FoundCells Is Nothing Then
    MsgBox "All cells are locked."
Else
    For Each r In FoundCells
    If Application.CheckSpelling(r.Value) Then
        Else: r.Select
        r.CheckSpelling CustomDictionary:="CUSTOM.DIC", _
        IgnoreUppercase:=False, AlwaysSuggest:=True, SpellLang:=3081
    End If
    Next r
End If

ActiveSheet.Protect Password:=""
End Sub
 
Thanks for the work Chihiro but there seems to be some glitching. The spell checker skips over many of the cells with errors. I can’t see why it would be doing this. Otherwise it is heading in the right direction.
 
Hmm, can you upload the sheet?

Chances are those cells skipped over strings.
Ex: T:LAK:A\ will be skipped whether you do spell check manually or by VBA.

You also might have "Ignore words that contain numbers" checked.
 
It is a field ticket template I am working on. As you will see I am using the same error throughout the page and some are picked up while others are skipped. Thanks for your time on this by the way. I am defiantly a noob at VBA.
 

Attachments

  • Updated Field Ticket test1.xlsm
    25.4 KB · Views: 1
Ah, I see where the issue is.

The sheet is using merged cell. The code will skip over those as it cannot retrieve value of merged cells.

In general, I don't recommend using merged cell unless you absolutely must. It adds unnecessary complexity to coding and will cause issues when referencing cells in formula.

Do you need those cells to be merged?
 
Yes we need the merged cells for the layout of the document. It is eventually exported to PDF and spacing and sizing of cells including merging has become necessary.

I do have the option of specifying what cells to scan for error but was hoping for a more generic solution.
 
Ok then, here's work around. It creates temp sheet to retain original format.
Unmerges all cells in original and goes through spell check routine and then pastes back the format and deletes temp sheet.

Code: 
Sub SelectUnlockedCells_Spellcheck()
Dim wsCopy As Worksheet
Dim wsOrig As Worksheet

ActiveSheet.Unprotect Password:=""
Set wsOrig = ActiveSheet
Set wsCopy = Sheets.Add

Application.CutCopyMode = False
wsOrig.Cells.Copy
wsCopy.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
wsOrig.Cells.UnMerge

Dim WorkRange As Range
Dim FoundCells As Range
Dim Cell As Range
wsOrig.Activate
Set WorkRange = ActiveSheet.UsedRange

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

Dim r As Range
If FoundCells Is Nothing Then
    MsgBox "All cells are locked."
Else
    For Each r In FoundCells
    If Application.CheckSpelling(r.Value) Then
        Else: r.Select
        r.CheckSpelling CustomDictionary:="CUSTOM.DIC", _
        IgnoreUppercase:=False, AlwaysSuggest:=True, SpellLang:=3081
    End If
    Next r
End If
Application.CutCopyMode = False
wsCopy.Cells.Copy
wsOrig.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

wsCopy.Delete
ActiveSheet.Protect Password:=""

End Sub
 
Thank you for all your time assisting me with this issue. I have had a look at how your edits function and it seems we have lost the ability to identify the cells with the errors again. Seems like a bit of a loop. I believe I will have to live with the fact that the spell check will just have to look at locked cells as well as the unlocked ones. It works flawlessly otherwise and our biggest issue is keeping the forms as simple to use as possible for our staff, some of whom are very new to computers.
 
This code should do what you are looking for.
Also skips Delete confirmation for temp sheet.

Code: 
Sub SelectUnlockedCells_Spellcheck()
Dim wsCopy As Worksheet
Dim wsOrig As Worksheet

ActiveSheet.Unprotect Password:=""
Set wsOrig = ActiveSheet
Set wsCopy = Sheets.Add

Application.CutCopyMode = False
wsOrig.Cells.Copy
wsCopy.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
wsOrig.Cells.UnMerge

Dim WorkRange As Range
Dim FoundCells As Range
Dim Cell As Range
wsOrig.Activate
Set WorkRange = ActiveSheet.UsedRange

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

Dim r As Range
If FoundCells Is Nothing Then
    MsgBox "All cells are locked."
Else
    For Each r In FoundCells
    If Application.CheckSpelling(r.Value) Then
        Else: r.Select
        r.Interior.ColorIndex = 3
        r.CheckSpelling CustomDictionary:="CUSTOM.DIC", _
        IgnoreUppercase:=False, AlwaysSuggest:=True, SpellLang:=3081
    End If
        r.Interior.ColorIndex = xlNone
    Next r
End If
Application.CutCopyMode = False
wsCopy.Cells.Copy
wsOrig.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.DisplayAlerts = False
wsCopy.Delete
Application.DisplayAlerts = True
ActiveSheet.Protect Password:=""

End Sub
 
We have a issue with our spam filter and cannot use excel files containing macros (.xlsm). Is there anyway we can accomplish spell check with .xlsx file format and protected sheets?
 
You must log in or register to reply here.
Back
Top