Spell Checker in a ActiveX Textbox issue.

[wdurrant]

I found some code I found at How to apply spell check in textbox? to check for misspelled words in an ActiveX textbox and all-in-all it works great except if it finds a misspelled word and you click on "Ignore Once" , "Ignore All" or "Cancel," it seems to go into this endless loop. After you hit Cancel about 10 times, the spell checker window finally goes away. I am not a VBA guru by any means. Any reasons why this may be happening?

Dim xObject As Object
Dim xCell As Range
On Error Resume Next
Set xCell = ActiveSheet.Cells(ActiveSheet.Rows.Count, ActiveSheet.Columns.Count)
If ActiveSheet.OLEObjects.Count > 0 Then
For Each xObject In ActiveSheet.OLEObjects
xCell = xObject.Object.Text
xCell.CheckSpelling , , , 1033
xObject.Object.Text = xCell
Next
End If

 

[6StringJazzer]

How many controls do you have in the active sheet? It will run spell check separately for each control. You run spell check on the first control. Then if you hit Cancel, it runs spell check on the next control. Then if you hit Cancel, it runs spell check on the next control. Until it has gone through all the controls.

Is this supposed to be a general-purpose macro that you can use for any sheet? Or do you have a specific sheet in mind that you need to check?

 

[wdurrant]

That makes sense. I have several controls on just one sheet but I only want it to check the one textbox that I have.

 

[wdurrant]

T

How many controls do you have in the active sheet? It will run spell check separately for each control. You run spell check on the first control. Then if you hit Cancel, it runs spell check on the next control. Then if you hit Cancel, it runs spell check on the next control. Until it has gone through all the controls.

Is this supposed to be a general-purpose macro that you can use for any sheet? Or do you have a specific sheet in mind that you need to check

How many controls do you have in the active sheet? It will run spell check separately for each control. You run spell check on the first control. Then if you hit Cancel, it runs spell check on the next control. Then if you hit Cancel, it runs spell check on the next control. Until it has gone through all the controls.

Is this supposed to be a general-purpose macro that you can use for any sheet? Or do you have a specific sheet in mind that you need to check?

How many controls do you have in the active sheet? It will run spell check separately for each control. You run spell check on the first control. Then if you hit Cancel, it runs spell check on the next control. Then if you hit Cancel, it runs spell check on the next control. Until it has gone through all the controls.

Is this supposed to be a general-purpose macro that you can use for any sheet? Or do you have a specific sheet in mind that you need to check?

 

[6StringJazzer]

Then instead of code that loops every control, use this (also I recommend marking your code by selecting it then clicking the VBA button--it preserves the spacing and color codes keywords)

Dim xCell As Range
Set xCell = ActiveSheet.Cells(ActiveSheet.Rows.Count, ActiveSheet.Columns.Count)
xCell = TextBox1 ' update with your text box's name
xCell.CheckSpelling , , , 1033
TextBox1.Text = xCell

 

[wdurrant]

Thank you very much!! Works perfect!!

 

[wdurrant]

Don't know if I need to start a new thread, but would you know how to highlight the misspelled word. Its not a huge deal, but it saves time trying to find the misspelled word and choosing the correct word to replace it with.

 

[6StringJazzer]

When you use .SpellChecking you put yourself in the hands of the built-in spell checker and you don't have any control over how it works. It is the same code as if you invoked spell check from the worksheet. To highlight the word you would have to write the spellcheck logic yourself from scratch. I do agree this is a weakness in how Excel spell check works.

 

[wdurrant]

Thanks for your response. Your help was much appreciated!