Spellcheck macro for multiple protected sheets

[Jasuan]

I am trying to create a macro that will run spell check on multiple protected sheets. Here is the code I have now that seems to just catch the Activesheets spelling errors, but then unprotects and then reprotects the remaining sheets without catching spelling errors in the defined ranges.

Sub SpellCheckIt()

ActiveSheet.Unprotect ("")
Range("B17", "B68:B147").CheckSpelling
ActiveSheet.Protect ("")

With Sheet4
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet6
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet8
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet10
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet12
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet14
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet16
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet18
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet20
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet22
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet24
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet26
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet28
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet30
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet32
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet34
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet36
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet38
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet40
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet42
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet44
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet45
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet46
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet47
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet49
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet50
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet51
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet52
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet53
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet54
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet55
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet56
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet57
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet58
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet59
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet60
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet61
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet62
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet48
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

With Sheet43
.Unprotect ("")
.Range("B22", "B31").CheckSpelling
.Protect ("")
End With

End Sub

 

[mhillmann]

The code looks good. Are you sure you have the names right for the sheets? VBA uses different sheet names than what is seen in the tabs. If that is right, then the code should work as intended.

Sorry I can't be more helpful.

 

[Jasuan]

The code looks good. Are you sure you have the names right for the sheets? VBA uses different sheet names than what is seen in the tabs. If that is right, then the code should work as intended.

Sorry I can't be more helpful.

If you look at the sheets properties window, it says "Properties - Sheet#" In that window there is a "(Name)" field and a "Name" field. I am using the "(Name)" field.

When the code runs, I can see it goes to the proper sheet very rapidly.

 

[mhillmann]

The code is running and spell checking for me. Here are the only things I can think of.

- There are no spelling errors, therefore the code runs without any sign the spell checking has occurred.
- There is a typo in the ranges somewhere and it is checking the wrong cells.
- There is an error occurring that I am unaware of.

Unfortunately that's all I can do. Maybe try a restart of excel and try again. If it still isn't working then I am not sure what the problem is.

 

[Jasuan]

The code is running and spell checking for me. Here are the only things I can think of.

- There are no spelling errors, therefore the code runs without any sign the spell checking has occurred.
- There is a typo in the ranges somewhere and it is checking the wrong cells.
- There is an error occurring that I am unaware of.

Unfortunately that's all I can do. Maybe try a restart of excel and try again. If it still isn't working then I am not sure what the problem is.

Does the fact that the range is a merged cell make a difference?

 

[mhillmann]

Merged cells do interact differently depending on the operation that you are trying to perform, but I don't think spell check is one of those. It should work regardless as long as you are calling the primary cell (the lowest row and column value in the cell).

i.e. If you have merged B31 through H31 as in the picture, you must refer to it as B31 in your code.

 

[mhillmann]

Oh I think I may have found it. You are referencing a block of cells, some that are merged and some that aren't. That (for some reason that I don't understand) is preventing spell check from operating correctly.

Change your code to this for each sheet:

With Sheet2
.Unprotect ("")
.Range("B22").CheckSpelling
.Range("B31").CheckSpelling
.Protect ("")
End With

 

[Jasuan]

Oh I think I may have found it. You are referencing a block of cells, some that are merged and some that aren't. That (for some reason that I don't understand) is preventing spell check from operating correctly.

Change your code to this for each sheet:

With Sheet2
.Unprotect ("")
.Range("B22").CheckSpelling
.Range("B31").CheckSpelling
.Protect ("")
End With

Almost there!

Now it is finding the mistakes in the merged cells. However, it is also now looking at cells that are not defined in the Range.

i.e. It is suggesting changes in E8 and H8 in the image from the previous post.

 

[mhillmann]

After some experimentation and forum browsing I have come up with an answer. (Granted you won't like it)

Looks like with the way SpellCheck is implemented, by default it checks from the selected cell to the end of the sheet. Which means, if you want to check a single cell, as in our example, you need to select it with a range of cells. Which we could do like this:

With Sheet2
.Unprotect
.Range("A1,A5").CheckSpelling
.Protect
End With

However, selecting a range of merged cells makes it impossible for spell check to operate properly.

Long story short, I don't think it is possible. But if it is, maybe someone else can help you with this. I unfortunately do not know a work around.

 

[Jasuan]

After some experimentation and forum browsing I have come up with an answer. (Granted you won't like it)

Looks like with the way SpellCheck is implemented, by default it checks from the selected cell to the end of the sheet. Which means, if you want to check a single cell, as in our example, you need to select it with a range of cells. Which we could do like this:

With Sheet2
.Unprotect
.Range("A1,A5").CheckSpelling
.Protect
End With

However, selecting a range of merged cells makes it impossible for spell check to operate properly.

Long story short, I don't think it is possible. But if it is, maybe someone else can help you with this. I unfortunately do not know a work around.

No worries mhillman. You have been a ton of help. Thanks so much for everything.