Spellcheck a TextBox

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.

Posted in Uncategorized

8 thoughts on “Spellcheck a TextBox

  1. 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.

  2. Andy: Hmmm. It seems I forgot to mention that SelectTb needs to be assigned to the textbox using Assign Macro.

  3. 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).

  4. 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

  5. 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.

  6. 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

  7. 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:

  8. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *