Spellcheck macro for multiple protected sheets

J

Jasuan

New Member
Joined
Jan 16, 2014
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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.
Code: 
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
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.
 
Upvote 0
mhillmann said:
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.
Click to expand...

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.
 
Upvote 0
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.
 
Upvote 0
mhillmann said:
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.
Click to expand...

Does the fact that the range is a merged cell make a difference?
SpellCheck_zpsqqj2e9rk.jpg
 
Upvote 0
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.
 
Upvote 0
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:

Code: 
With Sheet2
.Unprotect ("")
.Range("B22").CheckSpelling
.Range("B31").CheckSpelling
.Protect ("")
End With
 
Upvote 0
mhillmann said:
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:

Code: 
With Sheet2
.Unprotect ("")
.Range("B22").CheckSpelling
.Range("B31").CheckSpelling
.Protect ("")
End With
Click to expand...
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.
 
Upvote 0
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:

Code: 
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.
 
Upvote 0
mhillmann said:
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:

Code: 
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.
Click to expand...

No worries mhillman. You have been a ton of help. Thanks so much for everything.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
VBA Sorting Column Issue
Replies
0
Views
357
mrstats_2288
M
F
  • Question
%Progress bar for calling other macros?
Replies
4
Views
1K
Fluff
Fluff
F
  • Question
Preventing Call codes from running multiple times.
2
Replies
13
Views
1K
Fluff
Fluff
F
  • Question
Removing a duplicate entry & preventing data entry in the cell that had the duplicate.
Replies
1
Views
444
FrancisM
F
T
  • Question
sum cells in sheets that meet criteria
Replies
5
Views
650
tomanton
T

Forum statistics

Threads
1,216,036
Messages
6,128,432
Members
449,452
Latest member
Chris87

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top