How to loop through all worksheets in a workbook and spell check them all before closing/saving?

[jberman]

HI There, I appreciate anyones help. I have a macro that should loop through all the worksheets in my workbook and run a spell checker on all the sheets before saving and/or closing the document. This is what I have so far but it doesn't seem to work. ALSO Note that my worksheet only has two sheets in it but both are password protected using the word "expenses" as the password.

Here is my code...
Private Sub Workbook_BeforeClose(Cancel As Boolean)Dim ws As Worksheets
With ActiveSheet
.Unprotect ("expenses")
For Each ws In Worksheet
Cells.CheckSpelling
Next
.Protect ("expenses")
End With
End Sub


second code....

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheets
With ActiveSheet
.Unprotect ("expenses")
For Each ws In Worksheets
Cells.CheckSpelling
Next
.Protect ("expenses")
End With
End Sub

 

[BobUmlas]

You need to protect/unprotect inside the loop, not outside.

 

[MARK858]

You also need to qualify Cells with ws.
For Example...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet

For Each ws In Worksheets
 ws.Unprotect ("expenses")
 ws.Cells.CheckSpelling
 ws.Protect ("expenses") 
Next

End Sub

or

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet

For Each ws In Worksheets

With ws
    .Unprotect ("expenses")
    .Cells.CheckSpelling
    .Protect ("expenses") 
Next

End Sub

 

[jberman]

You need to protect/unprotect inside the loop, not outside.

Thank you guys very much! it worked!

 

[jberman]

Thank you so much!