CheckSpelling a Textbox

[Stuart]

Form is shown via an addin. User enters data into a textbox. When they exit
the textbox this code runs:

Private Sub TbFaxMsg_Exit _
(ByVal Cancel As MSForms.ReturnBoolean)
With Workbooks("G&H Project.xla")
.Worksheets("Fax Template").Unprotect
.Worksheets("Fax Template").Range("B22").Value _
= Me.TbFaxMsg.Value
.Worksheets("FaxTemplate") _
.Range"B22").CheckSpelling
End With
With Me.TbFaxMsg
.Value = Workbooks("G&H Project.xla") _
.Worksheets("Fax Template").Range _
("B22").Value
End With
End Sub

This seems to work, but can I suppress the message
suggesting checking all the sheet, please?

Regards.

 

[Tom Ogilvy]

Vic Eldridge Previously said:
ActiveX textboxes don't appear to have a spellchecking method, but the
Range object does, so I think you'll need to copy the text to a range first.
The following worked OK for me, with the code placed in Userform1's module.

Private Sub CommandButton1_Click()
Range("A1") = UserForm1.TextBox1.Text
Range("A1").CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True, _
SpellLang:=3081
Range("A1").ClearContents
AppActivate ("Userform1")
End Sub


If you don't like the idea of copying the text to the worksheet first,
you can use the Application.CheckSpelling syntax to spellcheck
each word, one at a time. This technique will not display the built-in
spellcheck dialog box and it also requires that you extract each
individual word out of the textbox's text.
If your textbox contains only one word, the following would work OK.

If Application.CheckSpelling _
(UserForm1.TextBox1.Text, _
"Custom.dic", True) = False Then _
MsgBox "The word was spelled incorrectly."


The second syntax will work OK when the textbox contains multiple words.
If however, you want to identify which word was incorrectly spelled, you
will
need to run Application.CheckSpelling on each individual word.

 

[Stuart]

I thought I was copying the textbox contents to a range
in one of the addin's sheets, and then running
Checkspelling on the contents of that range, before
transferring the revised data back to the textbox.

I missed something again?

Regards and thanks.

 

[Tom Ogilvy]

Yes you are and I am aware of that, but I misunderstood what Vic was
suggesting. So rather than being able to take the easy way out, you forced
me to do some testing. I think you need to have one cell adjacent to B22
that is empty, then you can do:

I will Assume B23 is emtpy.

Private Sub TbFaxMsg_Exit _
(ByVal Cancel As MSForms.ReturnBoolean)
With Workbooks("G&H Project.xla")
.Worksheets("Fax Template").Unprotect
.Worksheets("Fax Template").Range("B22").Value _
= Me.TbFaxMsg.Value
.Worksheets("FaxTemplate") _
.Range("B22").Resize(2).CheckSpelling
End With
With Me.TbFaxMsg
.Value = Workbooks("G&H Project.xla") _
.Worksheets("Fax Template").Range _
("B22").Value
' just for neatness let's remove the text from B22
Workbooks("G&H Project.xla") _
.Worksheets("Fax Template").Range _
("B22").ClearContents
End With
End Sub

By using two cells to check (one just a dummy), it doesn't appear to give
the prompt - at least this worked for me. xl2003

 

[Dick Kusleika]

Andy Miller said Application.DisplayAlerts will suppress that message too.

http://www.dicks-blog.com/archives/2004/08/02/spellcheck-a-textbox/

Adding that extra cell is pretty cool, though. They don't even have to be
contiguous

?union(range("a1"),range("IV65536")).CheckSpelling

appears to work in xl2000.

 

[Stuart]

Many thanks to you both.

Regards.

Yes you are and I am aware of that, but I misunderstood what Vic was
suggesting. So rather than being able to take the easy way out, you
forced
me to do some testing. I think you need to have one cell adjacent to B22
that is empty, then you can do:

I will Assume B23 is emtpy.

Private Sub TbFaxMsg_Exit _
(ByVal Cancel As MSForms.ReturnBoolean)
With Workbooks("G&H Project.xla")
.Worksheets("Fax Template").Unprotect
.Worksheets("Fax Template").Range("B22").Value _
= Me.TbFaxMsg.Value
.Worksheets("FaxTemplate") _
.Range("B22").Resize(2).CheckSpelling
End With
With Me.TbFaxMsg
.Value = Workbooks("G&H Project.xla") _
.Worksheets("Fax Template").Range _
("B22").Value
' just for neatness let's remove the text from B22
Workbooks("G&H Project.xla") _
.Worksheets("Fax Template").Range _
("B22").ClearContents
End With
End Sub

By using two cells to check (one just a dummy), it doesn't appear to give
the prompt - at least this worked for me. xl2003