Stop Spellchecker

[Brian900]

Does anyone know how to stop the spellchecker after it has checked specific cells? I have two cells, G3, G6. I would like to check only these areas and close the spellchecker. This is my code presently:

Private Sub CommandButton2_Click()

ActiveSheet.Unprotect Password:="PW"
ActiveSheet.Cells(3, 7).Cells(6, 7).CheckSpelling
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="PW"

End Sub

Thanks in advance, Brian.

 

[Fornatian]

Well Brian, the following works for me.


Range("G3","G6").CheckSpelling

 

[Brian900]

Ian,

Thanks for the reply. We're becoming very familiar with each other.

I previously tried your idea and it does start at the specific cell but it wants to continue spellchecking the entire file. I ONLY want it to check these two cells. The reason for this is that the rest of the file it protected to prevent accidental changes to the layout. The user only inputs information into these cells. Any other ideas?

Brian

 

[Fornatian]

It works in excel 97 so I don't undertstand why it wont work for you.

See attached, note that the activate event doesn't trigger when the attached sheet is opened you need to click on Sheet2 then back to Sheet1.

Another alternative may be to copy the contents(edit-paste-special-values) of the two cells to a different sheet, spellcheck the sheet then, if the contents different, overwrite the original with the values from the spellchecking sheet.

 

[Brian900]

I downloaded your file and it works as I need it to. I attempted to duplicate your code under a new file without success. The spellchecker will not stop after A1. I'm using Excel 2K, do you think that is the problem? I have attached the file where I tried to dup your code. Let me know if it works for you. Maybe I forgot something. Thanks, Brian.

 

[Fornatian]

Brian,

Works on my PC. Must be another A2K advance. Just been looking on www.MrExcel.com who suggest taking the protection off, running the spell check then reprotecting.

http://www.mrexcel.com/board/viewtopic.php?topic=16909&forum=2

 

[Brian900]

Ian,

You are probably right, it's an Excel 2K issue. Did you try the file I attached and did it work on yours? Also, I have previously removed the protection, ran the spellcheck and then added protection all using vba. It also wants to continue to spell check the entire sheet. I only want to spellcheck 3 areas and not be prompted to check anything else.

I may have lost this battle. The first defeat is always the hardest.

Thanks, Brian.

 

[Brian900]

After I have spellchecked "A4" and "A6" I get this question, "Do you want to continue Spellchecking from the top of sheet?"? If I can set the default value of this question to"false" then I would be accomplishing what I set out to do. Any ideas?

Brian

 

[Fornatian]

try:

application.DisplayAlerts = false

'your code

application.DisplayAlerts = true

not sure if it will work because I can't replicate the problem.

 

[Brian900]

I thought I was on to something there. Application.DisplayAlerts let it automatically begin checking from the top of the sheet. Any other thoughts?

Brian

 

[Brian900]

Ian,

Time to put this thing to rest. I got it working now. What I did was place three command buttons on my sheet. Each one would perform the spellcheck operation for their respective cells. I used three modules "Mod1" (CheckCells1), "Mod2" (CheckCells2) and "Mod3" (CheckCells3). For some reason, I had to reference a cell prior to the one I want to check in order to get it to stop. Otherwise it wanted to continue to spellcheck. Here is what I used:

Mod1

Public Sub CheckCells1()
Range("A3:B4", "A2").CheckSpelling
End Sub

Mod2

Public Sub CheckCells2()
Range("A6:B7", "A5").CheckSpelling
End Sub

Mod3

Public Sub CheckCells3()
Range("A9:B10", "A8").CheckSpelling
End Sub

Seems the long way to get a small job done. I must now move on because so much time was spent on this one. Thanks for your efforts. Brian.

 

[Fornatian]

Just to prettify your form you could have one button that called all three modules.


Public Sub OneButton_Click()
CheckCells1
CheckCells2
CheckCells3
End sub