spell check

A

ana

I have an appraisal form on excel that has formulas that
I want to protect and have locked them. But there is
also a range where the users must write comments. They
would like to spell check their comments but once I
protect the worksheet spell check no longer works. Is
there anyway to spell check a locked worksheet?
 
F

Frank Kabel

Hi
one way would be to record a macro which does the following:
- unprotect the workbook
- run the spellchecker
- protect the worksheet again
 
D

Debra Dalgleish

You could copy all the comments to another sheet, allow users to run a
spellcheck there, then copy the modified comments back to the original
cells.

To copy comments to another sheet:
'==============================
Sub ShowCommentsAllSheets()
'from code posted by Dave Peterson 2003-05-16
Application.ScreenUpdating = False

Dim commrange As Range
Dim mycell As Range
Dim ws As Worksheet
Dim newwks As Worksheet
Dim i As Long
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="Pwd123456"
Next ws

Set newwks = Worksheets.Add
newwks.Name = "CheckComments"
newwks.Range("A1:E1").Value = _
Array("Sheet", "Address", "Name", "Value", "Comment")

For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
Set commrange = ws.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If commrange Is Nothing Then
'do nothing
Else

i = newwks.Cells(Rows.Count, 1).End(xlUp).Row

For Each mycell In commrange
With newwks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = ws.Name
.Cells(i, 2).Value = mycell.Address
.Cells(i, 3).Value = mycell.Name.Name
.Cells(i, 4).Value = mycell.Value
.Cells(i, 5).Value = mycell.Comment.Text
End With
Next mycell
End If
Set commrange = Nothing
Next ws

'format cells for no wrapping, remove line break
newwks.Cells.WrapText = False
newwks.Columns("E:E").Replace What:=Chr(10), _
Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="Pwd123456"
Next ws

Application.ScreenUpdating = True

End Sub
'=======================================

To copy comments to original cells:
'===========================
Sub CommentsBackToSheet()
Dim c As Range
Dim ws As Worksheet
Dim r As Long
Application.DisplayAlerts = False

For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="Pwd123456"
Next ws
r = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For Each c In ActiveSheet.Range(Cells(2, 1), Cells(r, 1))
Sheets(c.Value).Range(c.Offset(0, 1).Value).Comment.Delete
Sheets(c.Value).Range(c.Offset(0, 1).Value) _
.AddComment Text:=c.Offset(0, 4).Value
Next c
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="Pwd123456"
Next ws
Sheets(1).Select
Sheets("CheckComments").Delete
Application.DisplayAlerts = True
End Sub
'=======================================
 
Top