I was helping someone on the newsgroups with spellchecking a textbox. The requirement was to spellcheck every time the user exited the textbox. It was a textbox from the drawing toolbar. Here’s what I came up with:
In a standard module:
Option Explicit
Public bTextSelect As Boolean
Sub SelectTb()
bTextSelect = True
Sheet1.Shapes(“Text Box 1?).Select
End Sub
In the sheet’s module:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If bTextSelect Then
Me.Shapes(“Text Box 1?).TopLeftCell.CheckSpelling
bTextSelect = False
End If
End Sub
This method has some problems. The spellchecker will ask if you want to check the rest of the sheet, which is not desirable. Also, because there’s a macro assigned to the textbox, the cursor doesn’t show up unless you select the text inside. I couldn’t figure out how to fix either of these.
Another way to spellcheck a textbox is to use the CheckSpelling method of the Application object (as opposed to the Range object used above). CheckSpelling when used with the Application object checks the word supplied as an argument and returns True if the word is in the dictionary and False otherwise. To use this method, you have to split the text into its component words and check each word individually. You also don’t get the benefit of the dialog which has suggestions, among other things. Here’s how this method might be implemented: The standard module code stays the same, but the sheet’s module changes.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim avWords As Variant
Dim i As Long
Dim sMsg As String
If bTextSelect Then
‘create an array of words
avWords = Split(Me.Shapes(1).TextFrame.Characters.Text, ” “)
For i = LBound(avWords) To UBound(avWords)
If Not Application.CheckSpelling(avWords(i)) Then
sMsg = sMsg & avWords(i) & vbNewLine
End If
Next i
If Len(sMsg) > 0 Then
sMsg = “The following words are not in the dictionary:” & _
vbNewLine & vbNewLine & sMsg
MsgBox sMsg, vbOKOnly, “Spellcheck”
End If
bTextSelect = False
End If
End Sub
Not very fancy, I know.
I didn’t know what your SelectTB sub was doing (I mean, I see what it’s doing, but not when it would be called). However, this little tidbit does the trick, I think:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If strTBText <> Me.Shapes(“Text Box 1?).TextFrame.Characters.Caption Then
Application.DisplayAlerts = False
Me.Shapes(“Text Box 1?).TopLeftCell.CheckSpelling
bTextSelect = False
Application.DisplayAlerts = False
strTBText = Me.Shapes(“Text Box 1?).TextFrame.Characters.Caption
End If
End Sub
I used strTBText as a Public variable, but I don’t think that you would even need to. I think that the sheet could actually hold it.
Andy: Hmmm. It seems I forgot to mention that SelectTb needs to be assigned to the textbox using Assign Macro.
In any case, it looks like setting Application.DisplayAlerts = False will prevent the messagebox asking if you want to spellcheck the rest of the sheet. I see now that I set it to False twice, rather than setting it to True at the end, as I meant to do (copy & paste can get one into trouble).
For a recent project, the client wanted spell checking of the textboxes in the userforms. I’d found an incomplete how-to on the web which made use of Word’s spell-checking capabilities. You actually open an instance of Word, but hide the window out of sight above the actual part of the desktop that the monitor sees (doesn’t work if the window is not visible). You copy the textbox contents into the hidden window and spell check it. If there’s a misspelled word, the familiar dialog pops up, otherwise, on to the next textbox.
The original web page I’d found (can’t find it now, but this might be a more recent version: http://msdn.microsoft.com/library/en-us/odc_wd2003_ta/html/odc_wdspellchkr.asp) left out some crucial bits, and I added more of my own. For instance, it’s not easy for the user to know something’s happening unless he gets the misspelling dialog. But I made the code select the contents of each textbox as a visible cue that it was working.
I finished it at 3:30 one morning, and the client was impressed. It was rather slick, I must admit.
– Jon
Jon – that had been the only way that I knew of to do spell-checking (I’ve never actually had a need for it myself).
I kinda thought that Dick’s method (once I cleaned it up a bit LOL) was pretty nice. Low overhead and not a lot in the way of code or complexity.
Andy –
Dick’s method (or your cleaned up version) is certainly a reasonable approach. My client needed something a bit fancier, so I built something fancier.
He also wants a macro to close a workbook that’s been opened with macros disabled, then reopen it with them enabled. Any ideas?
– Jon
He also wants a macro to close a workbook that’s been opened with macros disabled, then reopen it with them enabled. Any ideas?
This is interesting. I think I came up with something that might work by using the workbook’s customproperties collection.
As far as I know, there is no real MacrosEnabled property, so I don’t know of an easy way of checking to see if the workbook was opened with macros enabled. Therefore, I had to resort to custom properties. The bad side of that is that this bit of code would need to go into every single workbook that you may need to check:
I have this solution : it also selects (highlight) as well as add color to the TextBoxes.
Range(“A_1?) & Range(“A_2?) refer to Linked Cells in TextBoxes
Private Sub TextBox1_GotFocus()
TextBox1.BackColor = RGB(255, 255, 153)
With TextBox1
.SelStart = 0
.SelLength = Len(.Text)
End With
End Sub
Private Sub TextBox1_LostFocus()
TextBox1.BackColor = RGB(255, 255, 255)
With TextBox1
.SelStart = 1
End With
End Sub
Private Sub TextBox2_GotFocus()
TextBox2.BackColor = RGB(255, 255, 153)
With TextBox2
.SelStart = 0
.SelLength = Len(.Text)
End With
End Sub
Private Sub TextBox2_LostFocus()
TextBox2.BackColor = RGB(255, 255, 255)
With TextBox2
.SelStart = 1
End With
End Sub
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 9 Then
TextBox2.Activate
Exit Sub
End If
If KeyCode = 13 Then
Sheets(“Sheet1?).Range(“A_1?).CheckSpelling
Exit Sub
End If
End Sub
Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 9 Then
Range(“B2?).Activate
Exit Sub
End If
If KeyCode = 13 Then
Sheets(“Sheet1?).Range(“A_2?).CheckSpelling
Exit Sub
End If
End Sub
Should any user find this useful, please let me know.