CheckSpelling a Textbox

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.

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.

--
Regards,
Tom Ogilvy


"Stuart" <[email protected]> wrote in message
news:urhNE1%[email protected]...
> 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.
>
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Stuart" <[email protected]> wrote in message
> news:urhNE1%[email protected]...
>> 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.
>>
>>
>
>

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

--
Regards,
Tom Ogilvy



"Stuart" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Stuart" <[email protected]> wrote in message
> > news:urhNE1%[email protected]...
> >> 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.
> >>
> >>
> >
> >
>
>

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

http://www.*****-blog.com/archives/2...eck-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.

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

Tom Ogilvy wrote:
> 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
>
>
> "Stuart" <[email protected]> wrote in message
> news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>>> 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.
>>>
>>> --
>>> Regards,
>>> Tom Ogilvy
>>>
>>>
>>> "Stuart" <[email protected]> wrote in message
>>> news:urhNE1%[email protected]...
>>>> 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.

Many thanks to you both.

Regards.

"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Stuart" <[email protected]> wrote in message
> news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>> > 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.
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> > "Stuart" <[email protected]> wrote in message
>> > news:urhNE1%[email protected]...
>> >> 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.
>> >>
>> >>
>> >
>> >
>>
>>
>
>