Solved: Spell Check an ActiveX TextBox?

[CaptRon]

I have run into a problem (AGAIN!) with Excel 2007. When I place a text box on a sheet from the drawing toolbar in Excel 2003, I can set the properties to lock the text box and unlock text to allow users to enter text. I can even spell check the text box via VBA. Very cool. This workbook was originally built in Excel 2003 and functioned perfectly there.

Problem #1. Once I close the workbook and open it in Excel 2007, I can't enter anything in the text box. The properties have been reset by Excel 2007 to lock both the text box and the text. Opening it again in Excel 2003 doesn't help. I have to reset the properties in Excel 2003 to unlock text.

Something else that's odd. If I leave a text value in the text box (while I'm repairing it in 2003), Excel 2007 does not reset the properties to locked text. It works fine. But if the user ever empties the text box completely, closes it and re-opens in 2007, they can't access the text box. It's locked again. It's as though 2007 will automatically lock any empty text box but won't lock one with even a single text character in it.

I've tried using VBA to reset the text box properties on the sheet activate event and even though the property shows to be "unlock text", apparently it isn't. The result is the same. I can't insert anything.

Problem #2. So, I deleted that text box and used the textbox control (activeX) found on the Developer Tab in 2007. I can set the properties to lock the control while enabling text input and it works in both Excel 2003 and 2007. But now my spell checking code doesn't work. Can you even spell check an activeX textbox?

I need to find a solution for either Problem #1 or Problem #2. Any help will be greatly appreciated.

Man, I hope Excel 2010 is an improvement.

Ron

[Bob Phillips]

Got a workbook Ron?

[CaptRon]

Got a workbook Ron?

Yep. Two of them. The v 3.1 copy has the original text boxes in it and I have "reset" it so it should work OK again in Excel 2003. I am leaving a little junk text in there so when you open it in 2007 it will work properly. Delete the text completely, close and re-open in 2007 and you likely won't be able to enter anything in the text boxes.

The v 3.2 copy has the textboxes in it from the Developer Tab. You can enter text in both 2003 and 2007 (the good news), but I can't figure out how to select this type of textbox for spell checking. May not be possible.

The word "OK" in cell O1 on the Start worksheet disables the security macro and the password for the workbook, worksheets, and VBE is "jfk".

I appreciate you taking a look at this.

Ron

[CaptRon]

Got a workbook Ron?

Here's the original workbook, v 3.1.

Ron

[Artik]

The v 3.2 copy has the textboxes in it from the Developer Tab. (...) May not be possible. It is possible, but at around.

Because I do not have at the moment XL 2K7, I improved code for a XL 2K3 and below. But it should work on all versions (I hope :) ).
Sub SpellCk35()
Dim TxtBox As MSForms.TextBox
Dim strTxt As String
Dim Wkb As Workbook
On Error GoTo HandleErrors
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Activate spell checker for all versions of Excel (probably ;-) )
With ActiveSheet
Set TxtBox = .TextBox1
strTxt = TxtBox.Text
Set Wkb = Workbooks.Add(Template:=xlWBATWorksheet)
With Wkb.Worksheets(1).Range("A1")
.Value = strTxt
.CheckSpelling CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True, _
SpellLang:=1033
TxtBox.Value = .Value
End With
Wkb.Close False
Set Wkb = Nothing
End With
Range("A1").Select '<-Are you sure this is needed?
MsgBox "Spell check complete. ", vbOKOnly, " Spell Check"
SpellCk35_Exit:
On Error GoTo 0
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Exit Sub
HandleErrors:
MsgBox "Error No. " & Err.Number & vbCr & _
"Description: (" & Err.Description & ")" & vbCr & _
"in procedure SpellCk35"
Resume SpellCk35_Exit
End Sub

Artik

[CaptRon]

Artik...... THANKS! I will be able to mark this as SOLVED. Your code works perfectly in Excel 2003 and Excel 2007.

I still don't know why the original text box (created in Excel 2003 from the drawing tools toolbar) wouldn't work in Excel 2007, but since the activeX control textbox functions in both versions and your code spell checks that type of textbox, I'm very satisfied.

Thanks for the help.

Ron