Pasted text will not spell check

[Patrick C. Simonds]

I am trying to spell check the contents of a TextBox on a UserForm.


MikeH was good enough to provide me this code last night which places the
text onto a hidden worksheet and the checks for spelling errors. The problem
is that spell checker will not find spelling error in the text pasted to the
worksheet. as a test (just to make sure the spell checker was running) I
placed a misspelled word in cell F2 and the spell checker did find that
misspelling. I removed the last line of Mike's code so that the text would
be left on the worksheet, just so I could try running the spell checker but
it still would not find the error in the text.




Sheets("Sheet2").Range("a1").Value = TextBox1.Value
Application.EnableEvents = False
Sheets("Sheet2").Range("a1").CheckSpelling
Application.EnableEvents = True
TextBox1.Text = ""
TextBox1.Text = Sheets("Sheet2").Range("a1").Value
Sheets("Sheet2").Range("a1").Value = ""

 

[Mike H]

Hi,

The final code I posted was this after I added a dummy cell to spell check
to ensure the spell checker never went on to check the rest of the worksheet.
I have tested this multiple times and can't find a problem with it. perhaps
you could expalin a bit more about what the issue is.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim DummyCell As Range
Set DummyCell = Range("IV65536")
Sheets("Sheet1").Range("a1").Value = TextBox1.Text
Application.EnableEvents = False
Union(DummyCell, Sheets("Sheet1").Range("a1")).CheckSpelling
Application.EnableEvents = True
TextBox1.Text = ""
TextBox1.Text = Sheets("Sheet1").Range("a1").Value
Sheets("Sheet1").Range("a1").Value = ""
End Sub

Mike

 

[Patrick C. Simonds]

I get the following error:

Method 'union' of object '_global' failed and the following line was
highlighted

Union(DummyCell, Sheets("Sheet2").Range("a1")).CheckSpelling

So in the interest of time and knowing there would be no other text on the
worksheet I tried to move on with you original
code:

Sheets("Sheet2").Range("a1").Value = TextBox1.Value
Application.EnableEvents = False
Sheets("Sheet2").Range("a1").CheckSpelling
Application.EnableEvents = True
TextBox1.Text = ""
TextBox1.Text = Sheets("Sheet2").Range("a1").Value
Sheets("Sheet2").Range("a1").Value = ""


This worked to a point. It would place the text in cell A1 and run the spell
checker, but it would not hit on the misspelled words in the text it placed
in cell A1. When it did not work, I decided, as a test, to place a
misspelled word in another cell and rerun the code. It would not find the
misspelled words in A1, but would find the misspelled word I placed in the
other cell.

As an additional test I removed the line which cleared the text from cell A1
and after running the code I went to Sheet2 and looked to ensure the text
was there, it was. Then I tried running the spell checker and it still did
not see the errors in the text in cell A1. I tried changing your code to
paste as Text as opposed to Value but that did not help. If I type a
misspelled word in A1 spell checker sees it.

Again thanks for your time and help.

 

[Mike H]

Patrick,

What version of Excel are you using?

Mike

 

[Patrick C. Simonds]

2007, but

I recreated my UserForm and now you code works as advertised. Not sure what
the problem was but now that it is working I will move on.

Thanks for your help.

 

[Patrick C. Simonds]

Discovered one of my problems, and boy don't I feel dumb. The text in the
TextBox must be in all caps. Well under proofing options, I had Ignore words
in uppercase set. So now I have added the following line to ensure I will
not have that problem on any other machines.


Application.SpellingOptions.IgnoreCaps = False