was a spelling change made in worksheet?

[Rooski]

I am using MS Excel 2000. I have created a protected worksheet to prevent users of the sheet from changing formulas, header columns and other cells that contain informational text. Users, however, do have access to other cells in this sheet where they can enter text. Protecting the worksheet disables the Spell Check feature of Excel so users couldn't run a spell check. I managed to overcome that problem by creating a "spell check" macro which is linked to a graphic on the worksheet such that when the user clicks on the graphic the macro is executed. When the user clicks on the "spell check" graphic, Excel's spelling dialog box is displayed provided there is a spelling error in the worksheet. If there is no spelling error encountered, the user sees nothing leading to doubt as to whether a spell check was indeed executed.

My macro "spell check" code is presented below. Is there any way to add some code that would send a message box back to the user stating that the spell check was executed but no spelling errors were encountered if that were the case?

Sub Spell_Check()

ActiveSheet.Unprotect

Cells.CheckSpelling CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True

ActiveSheet.Protect Contents:=True

End Sub

Thanks in advance for your time,

Rooski

 

[VBAjedi]

I'd just use:

Sub Spell_Check()

ActiveSheet.Unprotect
    Cells.CheckSpelling CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True
    Msgbox("Spelling check complete")
ActiveSheet.Protect Contents:=True

End Sub

Is there some reason that wouldn't work?

VBAjedi

 

[Rooski]

Thank you for the quick response. As you can probably tell, I have no experience writing visual basic code. I do, however, have a programming background. The code I used in the spell check macro was adapted from a thread I found in tek-tips.

Your suggestion was very helpful and it does work. What I would really like to know, however, is whether Visual Basic is capable of querying a spreadsheet function to determine whether the spell check macro enountered any spelling errors at all. In that situation, I would like to send back a message box that displays "Spelling check complete. No spelling errors detected." I suppose this will involve some sort of "if, then, else" construct.

This is not a very high priority and your suggested solution is more than adequate as it doesn't leave the user wondering whether her or she even invoked the spell check.

Again, thanks for your very simple and elegant solution.

 

[PHV]

The CheckSpelling method of the Range object doesn't return any value at all ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Rooski]

OK. That settles it then. Thanks to you and VBAjedi for all your help.

Rooski