Spell Check in Protected Worksheet & Shared Workbook

[Guest]

I have successfully used the code from a previous discussion post to activate
the spell check function in a protected worksheet.

However when I share the workbook and run the macro the error: "400"
appears. When I go to the Help option the help screen is blank.

Does anyone have any idea how I can use the spell check in a protected
worksheet within a shared workbook?

The code used from the previous discussion is shown below.

Many thanks
Dave

Cats

Requires VBA to unprotect the sheet, do the spellcheck then reprotect the
sheet.

Similar to.......

Sub Spell_Check()
ActiveSheet.Unprotect Password:="justme"
Cells.CheckSpelling SpellLang:=1033
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub


Gord Dibben Excel MVP

 

[Dave Peterson]

You can't change protection of a worksheet in a shared workbook.

 

[Guest]

Does this mean it is impossible to use spell check in a protected and shared
workbook?

 

[Dave Peterson]

I think it's impossible to use that version of spellcheck in a protected
worksheet in a shared workbook.

But there is an application.spellcheck (see VBA's help) that will check word by
word.

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim myCell As Range
Dim mySplit As Variant
Dim iCtr As Long
Dim AllWordsOk As Boolean

Set wks = ActiveSheet

With wks
For Each myCell In .UsedRange.Cells
If myCell.Locked = True Then
'do nothing
Else
mySplit = Split(myCell.Value, " ")
AllWordsOk = True
For iCtr = LBound(mySplit) To UBound(mySplit)
If Application.CheckSpelling(mySplit(iCtr)) = False Then
AllWordsOk = False
Exit For
End If
Next iCtr
If AllWordsOk Then
'do nothing
Else
MsgBox "Please fix cell: " & myCell.Address(0, 0)
End If
End If

Next myCell
End With

End Sub

I split the value in each cell by a space character. This may not be sufficient
for your data.

 

[Guest]

Thanks Dave

Although not ideal it is better than no spell check.

Best regards
Dave

 

[Dave Peterson]

Hope you're still there.

I checked each word (separated by a space). You don't need to go to that level:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim myCell As Range

Set wks = ActiveSheet

With wks
For Each myCell In .UsedRange.Cells
If myCell.Locked = True Then
'do nothing
Else
If Application.CheckSpelling(myCell.Value) = False Then
MsgBox "Please fix cell: " & myCell.Address(0, 0)
End If
End If
Next myCell
End With

End Sub

Thanks Dave

Although not ideal it is better than no spell check.

Best regards
Dave

 

[Guest]

Dave

Yes I am still here; thanks very much for the simpler version.

Best regards
Dave