Spell check unprotected cells in a protected worksheet

Hi there,

Try the following code and see if it does what you need:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

I will try this out and see. Keeping fingers crossed.

Ok, please keep me informed.

Greg M

Greg

I tried the code and it works.... that is until I protect the sheet. I get an error message and the debugger highlites the following line:

rCell.CheckSpelling SpellLang:=2057

Would it be better to specifically name the range I am working with? In the sheet the range is $R$7:$R$340. Not being a great VB coder (like yourself where would I include this information? Would I need to create a range of just the unprotected cells? That would be a pain for sure. (They are not sequential) So far so good. I look forward to your reply.

Brian.

here is a copy of the error message:

Run-time error '1004':

CheckSpelling method of Range class failed

Hi again,

I can't believe I never realised that spellchecking can't be performed while a worksheet is protected

The following code provides a workaround for the above restriction. The code is very slightly more sophisticated than a simple temporary unprotecting of the worksheet, because it checks only those cells which are unlocked - this means that Users will not have any possibility of correcting misspellings (or words which Excel "thinks" are misspellings) which are located in locked cells.

Please Login or Register  to view this content.

Hope this workaround is useful for you - as always, please keep me informed.

Regards,

Greg M

Success!! Your code worked well. Thank you!! And my users will thank you!!!

Only one thing, the macro does expose your password. I will remove the developer tab from the ribbon, but it is still a security risk as you can't protect the ribbon from edits?

Or can you????

Brian.

Hi again,

Many thanks for your feedback - glad I was able to help.

There are various ways of making your password more secure - the more "savvy" your Users are, the better it has to be hidden.

Sometimes I "hide" a password on a VeryHidden worksheet or as a Defined Name on a VeryHidden worksheet, and write a function to extract the value from there. This obviously isn't secure if your Users know how to read VBA code. A more secure way is to password-protect the VBA Project itself - you can do this using the VBA Project Properties DialogBox - it can be a bit of a pain when you're at the development stage because you need to enter the password every time you open the workbook. Anyway, some food for thought!

Anything else, feel free to ask.

Best regards,

Greg M

Hi All

I know this is an old thread but wondering if anyone is still around to be able to help. I used the above code in a test sheet and it worked perfectly but when I put it into my sheet it didnt pick up the spelling mistakes. The cells are merged. When I unmerged the cells it picked them up. Is there anything I can do so it picks up the spelling mistakes in merged cells. the sheet has titles and reference cells above the data entry so need the merged cells for formatting purposed

Thanks