Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Spellcheck in Excel

  Asked By: Jasmine    Date: Aug 11    Category: MS Office    Views: 1623
  

I have created an Excel worksheet for others to use and add text and
budget numbers to. I have protected cells with formulas so that they
will not be inadvertantly changed.

The problem is that users cannot spell check the worksheet after they
have added text in the unprotected cells since I have the worksheet
protected. I understand why they should not be able to check and
change cells that are protected, but I would like for them to be able
to spellcheck the entries that they have made.

Does anyone know a way that I can check or uncheck something when I
protect the sheet to allow this? (I have tried several things but
spell check was always greyed out when the sheet is protected.)

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Jimmy Abp     Answered On: Aug 11

I think the only workaround for this would be to make a macro which:

Unprotects the sheet
Runs the spellcheck
Protects the sheet.

This macro could be assigned to the existing button or a new one. You would
need a macro to run on opening the workbook to assign it and another which
ran on closing the workbook to unassign it.

I think there may be an issue that if there is an error the sheet  may be
accessible to your users  in an unprotected state. i.e. the spellcheck will
stop for the correction to be made  and the user could then do something
else.

 
Answer #2    Answered By: Rickey Scott     Answered On: Aug 11

Thank you for the information. At this time, I think I am willing to deal
with the misspelling rather than to risk their having access to an
unprotected sheet. Thank you for adding the information that it could
happen as it checked. I appreciate it.

 
Answer #3    Answered By: Monique Perry     Answered On: Aug 11

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

 
Didn't find what you were looking for? Find more on Spellcheck in Excel Or get search suggestion and latest updates.




Tagged: