Can I do this with a TextBox?

[Stuart]

User opens a book, containing a single sheet. Code in the
Workbook_Open Event runs and if certain conditions are
met then a TextBox created by the Drawing toolbar is
displayed and enabled for data entry.

User enters their data, and when they click a cell outside
the textbox (ie exit the textbox) CheckSpelling will
automatically run on just the textbox.

Having done so, user would be free to re-enter the
textbox, but again, CheckSpelling would run when they
exit.

An ActiveX textbox is fine, except that it doesn't offer the
bells and whistles features of Spell Checking.

Hope this makes sense.

Regards.

 

[Dick Kusleika]

Stuart

In a standard module

Public bSelectTB As Boolean

Sub SelectTb()

bSelectTB = True
Sheet1.Shapes("Text Box 1").Select

End Sub

Assing SelectTb to the textbox and change this code to reflect the correct
sheet and textbox name. Then, in the sheet's module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If bSelectTB Then
Me.Shapes("Text Box 1").TopLeftCell.CheckSpelling
bSelectTB = False
End If

End Sub

Again change the textbox's name. This worked for me with limited testing,
but let me know if you have any problems.

 

[Stuart]

Many thanks and apologies for my late response.

I have had problems. When I am reasonably sure
of the issues, I will post a new thread, in view of
the likely time lapse.

The issues are not related to your code, but I
believe more related to the way in which I
then use the code.

Again many thanks.

Regards.

 

[Dick Kusleika]

I will post a new thread, in view of
the likely time lapse.

Okay. If you like, you can feel free to reply to this thread. I keep a
close eye on threads I'm involved with.

 

[Stuart]

Resolved, I think. It was a protection issue. Couldn't find a way to
enable just the textbox, so had to unprotect the sheet. Then it
worked. I'm now using:

Option Explicit
Public bSelectTB As Boolean

Sub SelectTb()
bSelectTB = True
Worksheets("Contract Master Fax").Unprotect _
Password:="abc"
Sheets("Contract Master Fax") _
.Shapes("Text Box 10").Select
End Sub

and

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If bSelectTB Then
Me.Shapes("Text Box 10").TopLeftCell.CheckSpelling
Worksheets("Contract Master Fax").Protect _
Password:="abc"
Worksheets("Contract Master Fax").EnableSelection _
= xlUnlockedCells
bSelectTB = False
End If
End Sub

When the user clicks a cell in the sheet to exit the textbox, they can click
any
cell (since at that point the sheet is unprotected. As long as the
Selection_
Change event fires it should be okay and protected again.

3 questions, please:

1. The textbox is positioned over the range "C26:L65". When the user exits,
CheckSpelling runs on the textbox, then prompts to check the rest of the
sheet. Can this be turned off?
2. When the user initially clicks within the textbox, the cursor is not
visible.
It's only after a second click that it appears. Can it appear the first
time?
3. Can the tab key be enabled?

Many thanks again.

Regards.

 

[Dick Kusleika]

3 questions, please:

1. The textbox is positioned over the range "C26:L65". When the user exits,
CheckSpelling runs on the textbox, then prompts to check the rest of the
sheet. Can this be turned off?

Unfortunately, no. See below for another option.

2. When the user initially clicks within the textbox, the cursor is not
visible.
It's only after a second click that it appears. Can it appear the first
time?

I hadn't realized that that was happening, but I confirmed that it does. I
tried various things to get it show, but couldn't come up with anything.

3. Can the tab key be enabled?

Inside the textbox? Control+tab can be used to tab inside the textbox.

As you've seen, there are some drawbacks to this spell check method. I
found a few more, like deselecting the textbox by selecting a protected cell
causes an error. That error can be trapped, I think, but it's a flaw in the
code that I gave you.

We're using the CheckSpelling method on a range object. You can also use
the CheckSpelling method of the Application object which will get rid of
some of those problems. However, it presents a whole new set of problems
and some additional coding. For instance, it only checks one word at a
time, so you have to split the text from the textbox into words and run the
method on those words. Doing this does not show the dialog with the
suggestions, it merely returns a True or False depending on the outcome of
the check.

Let me know if you want to investigate this alternative method.

 

[Stuart]

Thanks again.
I originally tried an ActiveX textbox (which seemed fine to me),
until my co-users insisted they wanted to run Spelling (F7) on it.
I then found that CheckSpelling won't work on that type of
textbox.
I think I found code for the technique you mention (ie to
split the textbox contents into individual words, and then to
checkspell them), but as you say, there are no helpful
suggestions for anything mispelled.
That's the main reason I changed to a textbox from the
Drawing toolbar....and I'm beginning to discover some of
the limitations!

One last question please:
How can I obtain a list of the Event options associated
with a textbox .... (and that goes for Objects/Controls
generally)? I'm sure I've seen a way to do this as soon
as the textbox (eg) is created?

So if I found a (say) Textbox_Deactivate or Deselect
event, I might be able to use that to check the cell the
user had exited to, and test that for being locked ......
if so, return user to the textbox.

Regards and thanks.

 

[Dick Kusleika]

One last question please:
How can I obtain a list of the Event options associated
with a textbox .... (and that goes for Objects/Controls
generally)? I'm sure I've seen a way to do this as soon
as the textbox (eg) is created?

Controls from the Forms or Drawing toolbars don't have events, only OnAction
(basically a click event). Only ActiveX controls have events like
Deactivate. If you want to avoid that specific error, I think you only need
to put your Unprotect line in the SelectionChange event. That will allow
the user to select a protected cell, but once the EnableSelection line is
run it will kick them to the next unprotected cell. I didn't test that, but
I think it will work.

If you need to find the event associated with ActiveX controls, the easiest
way is to open the sheet module that contains the controls. Use the left
pulldown to find the control and right pulldown will show all the events.

 

[Stuart]

Many thanks for all the help.

Regards.

Controls from the Forms or Drawing toolbars don't have events, only OnAction
(basically a click event). Only ActiveX controls have events like
Deactivate. If you want to avoid that specific error, I think you only need
to put your Unprotect line in the SelectionChange event. That will allow
the user to select a protected cell, but once the EnableSelection line is
run it will kick them to the next unprotected cell. I didn't test that, but
I think it will work.

If you need to find the event associated with ActiveX controls, the easiest
way is to open the sheet module that contains the controls. Use the left
pulldown to find the control and right pulldown will show all the events.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

 

[Dick Kusleika]

Stuart

See Andy's comments here

http://www.dicks-blog.com/excel/2004/08/spellcheck_a_te.html

It seems that setting DisplayAlerts to False will prevent that "check rest
of sheet" message. I haven't tested it, but Andy's pretty solid.