I had the same problem with a Word protected document. Here are some
pieces of code that I used to solve the problem. You should be able
to modify for an Excel spreadsheet. CheckSpelling was attached to a
toolbar button. SavedText looked for the formfields in the Word
document that my users could actually change, CheckPassword unlocked
and relocked the document while DoFindReplace actually allowed them
to correct any misspellings.
One thing I noticed I did not add was an error handler to this code.
What I would do was add an on error handler to use CheckPassword to
relock the spreadsheet.
Hope this helps.
Sub CheckSpelling()
Application.ScreenUpdating = False
iCount = 0
'open locked document
Call CheckPassword
'build array of all text fields and their result
Call SavedText(wdFieldFormTextInput)
'change all the hyphens
With ActiveDocument.Content.Find
.ClearFormatting
With .Replacement
.ClearFormatting
End With
.Execute FindText:="'", ReplaceWith:="'", Format:=True, _
Replace:=wdReplaceAll
End With
'change all the quotes
With ActiveDocument.Content.Find
.ClearFormatting
With .Replacement
.ClearFormatting
End With
.Execute FindText:=""", ReplaceWith:=""", Format:=True, _
Replace:=wdReplaceAll
End With
Application.ScreenUpdating = True
ActiveDocument.CheckSpelling
doFindReplace iCount, fField, fFieldText()
End Sub
Public Function SavedText(Optional FieldType As Integer) As String()
For Each fField In ActiveDocument.FormFields
If fField.Type = FieldType Then
ReDim Preserve fFieldText(1, iCount)
fFieldText(0, iCount) = fField.Result
fFieldText(1, iCount) = fField.Name
fField.Select
Selection.TypeText "<" & fFieldText(0, iCount) & ">"
iCount = iCount + 1
End If
Next fField
SavedText = fFieldText
End Function
Public Sub CheckPassword()
If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
ActiveDocument.Unprotect "password"
Else
ActiveDocument.Protect wdAllowOnlyFormFields, True, "password"
End If
End Sub
Public Sub doFindReplace(iCount As Integer, fField As FormField,
fFieldText() As String)
Dim i As Integer, intRetVal As Integer
Dim strNewText As String
Selection.HomeKey Unit:=wdStory
Application.ScreenUpdating = False
For i = 0 To iCount - 1
Selection.Find.ClearFormatting
With Selection.Find
.Text = "<"
.Forward = True
.Wrap = wdFindContinue
.MatchCase = False
.Execute
End With
Selection.Extend
Selection.Extend Character:=">"
intRetVal = StrComp(Selection.Text, fFieldText(0, i))
Select Case intRetVal
Case 0
Set fField = Selection.FormFields.Add _
(Range:=Selection.Range,
Type:=wdFieldFormTextInput)
fField.Result = fFieldText(0, i)
fField.Name = fFieldText(1, i)
Case Else
intLength = Len(Selection.Text)
strNewText = Mid(Selection.Text, 2, intLength - 2)
Set fField = Selection.FormFields.Add _
(Range:=Selection.Range,
Type:=wdFieldFormTextInput)
fField.Result = strNewText
fField.Name = fFieldText(1, i)
End Select
Next i
Call CheckPassword
Application.ScreenUpdating = True
MsgBox "Spell Check Complete.", vbOKOnly, TITLE
End Sub