Spellchecking excel form textbox 2

[mileslowe]

I read the question from thread707-1087775 and cannot get the suggestions to work. Here is the code that I have been trying to use (I have coppied an excel cell value to a form and want to spell check it):

Private Sub CommandButton2_Click()

'Check HoY Spelling

frmEditValues.tbxCellValue.SetFocus

If (Application.CheckSpelling(frmEditValues.tbxCellValue.Text) = True) Then
    
    MsgBox ("spelling OK")
    
        cmdSpellXtra.BackColor = &HFF00&
      Else
         'NOT OK - Copy text data to hidden XLA 'SpellCheck' worksheet
        Sheets("2007").Select
        Range("F7").Select
        Selection.Copy
        Sheets("SpellCheckSheet").Select
        Range("A1").Select
        ActiveSheet.Paste
        Range("E15").Select
        Worksheets("SpellCheckSheet").CheckSpelling AlwaysSuggest:=True
              cmdSpellXtra.BackColor = &HFF&
                  strSpellCheckXtra = tbxCellValue.Text
                        Application.Worksheets("SpellCheck").Cells(1, 1).Value = strSpellCheckXtra
                        'Now Check spelling
                        Worksheets("SpellCheck").CheckSpelling AlwaysSuggest:=True
                        tbxCellValue.Text = ThisWorkbook.Worksheets("SpellCheck").Cells(1, 1).Text
    End If

End Sub

 

[SkipVought]

On what statement is the code failing?

I got this to run...

Private Sub CommandButton2_Click()
    Dim strSpellCheckXtra
'Check HoY Spelling

frmEditValues.tbxCellValue.SetFocus

If (Application.CheckSpelling(frmEditValues.tbxCellValue.Text) = True) Then
    
    MsgBox ("spelling OK")
    
        cmdSpellXtra.BackColor = &HFF00&
      Else
         'NOT OK - Copy text data to hidden XLA 'SpellCheck' worksheet
        Sheets("2007").Range("F7").Copy
        Sheets("SpellCheckSheet").Range("A1").PasteSpecial xlPasteAll
        
        Worksheets("SpellCheckSheet").CheckSpelling AlwaysSuggest:=True
        cmdSpellXtra.BackColor = &HFF&
        strSpellCheckXtra = tbxCellValue.Text
        Application.Worksheets("SpellCheck").Cells(1, 1).Value = strSpellCheckXtra
        'Now Check spelling
        Worksheets("SpellCheck").CheckSpelling AlwaysSuggest:=True
        tbxCellValue.Text = ThisWorkbook.Worksheets("SpellCheck").Cells(1, 1).Text
    End If

End Sub

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[mileslowe]

Thanks for getting back to me on this. I load text from a cell into the form text box and then click on a check spell control button and I get a runtime error 13 on the following line:

If (Application.CheckSpelling(frmEditValues.tbxCellValue.Text) = True) Then

The form name is: frmEditValues, the text box holding the text is named: tbxCellValue.

 

[rmikesmith]

mileslowe,

After my replies in the thread you referenced, I worked with the original poster on this offline. I subsequently discovered a problem using Application.CheckSpelling. According to Excel VBA Help, Application.CheckSpelling is used to check a single word. However, this method will check a multi-word string with the (also) undocumented limit of 255 characters. If the supplied string is longer than this, a 'Type Mismatch' error is triggered (I don't know why that particular error). I wrote a function to overcome this:

Function SpellingIsCorrect(ByVal strInput) As Boolean
Dim i As Long
Dim lLength As Long
Dim lWordLen As Long
Dim strTemp As String
Dim vWords As Variant
Dim sArr() As String
Dim iCount As Integer

   'If terxt string > 255 characters, Type Mismatch error occurs
   If Len(strInput) < 256 Then
     SpellingIsCorrect = Application.CheckSpelling(strInput)
     Exit Function
   End If

   'otherwise parse individual words
   vWords = Split(strInput, Chr(32), -1, vbBinaryCompare)

   strTemp = ""
   lLength = 0
   iCount = 0

   For i = LBound(vWords) To UBound(vWords)
     lWordLen = Len(vWords(i))
     If lLength + lWordLen < 256 Then
       strTemp = strTemp & vWords(i) & ""
     Else
       iCount = iCount + 1
       ReDim Preserve sArr(1 To iCount)
       sArr(iCount) = strTemp
       strTemp = ""
     End If
     lLength = Len(strTemp)
   Next i

   If lLength > 0 Then
     iCount = iCount + 1
     ReDim Preserve sArr(1 To iCount)
     sArr(iCount) = strTemp
   End If

   SpellingIsCorrect = True
   For i = 1 To iCount
     If Not Application.CheckSpelling(sArr(i)) Then
       SpellingIsCorrect = False
       Exit For
     End If
   Next i
    
End Function

Keep in mind this function (and Application.CheckSpelling itself) only returns True/False depending on if the supplied string is spelled correctly (actually, if the words are found in the specified dictionary). It does not provide a dialog where corrections can be made. You will need a different strategy for that.

Hope this helps.
Mike

 

[SkipVought]

Mike,

Nice touch!

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[rmikesmith]

Thanks Skip.

I had seen this post a day or so ago but had to dig out my old code.

By the way, the Range.CheckSpelling method also has a limit but it's about 910 or 911 characters (MS is nothing if not consistent ). It won't throw an error, it just doesn't make any corrections the user has supplied.


Regards,
Mike

 

[SkipVought]

Lots of GOOD info. Thanx! ==> *

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[mileslowe]

Mike, I have been working on this project all night and just now added your module update and I still get an error. Here is what I am doing:

I have a cell F7 that has a lot of medical memo data in it and copy that information to my userform to check spelling and the goal will be to bring the corrected text back to that original cell from the user form. Here is my code now:

Private Sub CommandButton2_Click()

Dim strSpellCheckXtra As String

Range("F7").Select
    Selection.Copy
    Sheets("SpellCheck").Select
    Range("A1").Select
    ActiveSheet.Paste
    Cells.CheckSpelling SpellLang:=1033
    Range("B2").Select
  
If SpellingIsCorrect(tbxCellValue.Text) Then
      frmEditValues.BackColor = &HFF00&
    Else
      'NOT OK - Copy text data to hidden XLA SpellCheck worksheet
      frmEditValues.BackColor = &HFF&
      strSpellCheckXtra = tbxCellValue.Text
      ThisWorkbook.Worksheets("SpellCheck").Cells(1, 1).Text = strSpellCheckXtra
      'Now Check spelling
      Sheets("SpellCheck").Cells(1, 1).CheckSpelling AlwaysSuggest:=True
      txtXtra.Text = ThisWorkbook.Worksheets("SpellCheck").Cells(1, 1).Text
      frmEditValues.BackColor = &HFF00&
End If
    
    Range("E15").Select

End Sub

I have to run an errand and will be back in an hour or so. I am getting an error at this code line:

ThisWorkbook.Worksheets("SpellCheck").Cells(1, 1).Text = strSpellCheckXtra

that error says:

Run-time error 1004
Unable to set the text property of the range class

 

[rmikesmith]

Yes, the Text property of the Range object is read-only. Use the Value property instead; like this:

ThisWorkbook.Worksheets("SpellCheck").Cells(1, 1).Value = strSpellCheckXtra

Regards,
Mike

 

[mileslowe]

I cleaned up the code and made a couple of recommended changes you suggested and now after running the spell check in the code of the hiddent spellcheck sheet, I made one change to a spelled word and finished the process. Then I noticed that the changed spelling to the word corrected did not change in the cell 1, 1 text?? Here is the code as used:

Private Sub CommandButton2_Click()

Dim strSpellCheckXtra As String
Dim iResponse As String

Range("F7").Select
    Selection.Copy
    Sheets("SpellCheck").Select
    Range("A1").Select
    ActiveSheet.Paste
    'Cells.CheckSpelling SpellLang:=1033
    Range("B2").Select
  
If SpellingIsCorrect(tbxCellValue.Text) Then
      frmEditValues.BackColor = &HFF00&
    Else
    
    iResponse = MsgBox("There were spelling errors found, do you want to continue?  ", _
        vbYesNoCancel + vbQuestion + vbApplicationModal + vbDefaultButton1, "")

    Select Case iResponse
        Case vbYes:
            'NOT OK - Copy text data to hidden XLA SpellCheck worksheet
            frmEditValues.BackColor = &HFF&
            strSpellCheckXtra = tbxCellValue.Text
            ThisWorkbook.Worksheets("SpellCheck").Cells(1, 1).Value = strSpellCheckXtra
            'Now Check spelling
            Sheets("SpellCheck").Cells(1, 1).CheckSpelling AlwaysSuggest:=True
            frmEditValues.tbxCellValue.Value = ThisWorkbook.Worksheets("SpellCheck").Cells(1, 1).Value
            frmEditValues.BackColor = &HFF00&
          
    End Select
      
End If
    
iResponse = MsgBox("Do you want to copy text back to spreadsheet cell location?  ", vbYesNo + vbQuestion + vbApplicationModal + vbDefaultButton1, "")

Select Case iResponse
    Case vbYes:
      ' Enter your code here
      ThisWorkbook.Worksheets("2007").Range("F7").Value = frmEditValues.tbxCellValue.Value
    Case vbNo:
      ' Enter your code here
End Select

End Sub

 

[rmikesmith]

Another Microsoft gotcha. See my response to Skip a couple of replies ago, regarding Range.CheckSpelling limit. I suspect that is what's happening in your case.

Just so happens that I needed to implement spell checking in a current project so I modified the approach in the code you are using to split the text into multiple cells, well below the text length limit. I'll dig that out but may not have it handy.


Regards,
Mike

 

[mileslowe]

I will check out your response. I just did some checking and after the cell text is copied to the userform textbox and then run the code on the hidden sheet I end up with a formula is too long error from the spellchecker. If I copy the hidden sheet text from A1 and paste it into notepad and copy out of notepad and back to cell Again, and run the spell checker manually again there is no error. Sounds like some kind of paste issues, I will now look at your response. Thanks for all your patience and helpfulness Mike!

 

[mileslowe]

I just retried copying to notepad and pasting back again and not luck, so the character limit was be the issue. My text in the 2007 sheet cell including spaces etc. was 1399.

 

[rmikesmith]

OK. The following procedure takes as input a worksheet cell to be spell checked. It uses a hidden, blank worksheet named SpellCheck. If the cell text is longer than 500 characters, it will be split into two or more cells, invoke the Worksheet.CheckSpelling method, re-combine the text fragments and clean up.

Sub SpellCheckRange(ByRef rngSource As Range)
Const MAXCHARS As Integer = 500
Dim iLength As Integer
Dim i As Integer
Dim iStartPos As Integer
Dim sText As String
Dim sSubString As String
Dim lRow As Long


   iLength = Len(rngSource.Value)
   If iLength = 0 Then Exit Sub
   
   If iLength > MAXCHARS Then
     sText = rngSource.Value
     lRow = 0
     Do Until Len(sText) <= MAXCHARS
       sSubString = sText
       For i = MAXCHARS To 1 Step -1
         If Mid$(sText, i, 1) = Chr(32) Then
           sSubString = Mid$(sText, 1, i)
           Exit For
         End If
       Next i
       sText = DeleteSubstring(sText, sSubString)
       lRow = lRow + 1
       SpellCheck.Cells(lRow, 1).Value = sSubString
     Loop
     If Len(sText) > 0 Then
       lRow = lRow + 1
       SpellCheck.Cells(lRow, 1).Value = sText
     End If
     With SpellCheck
       .CheckSpelling IgnoreUppercase:=False, AlwaysSuggest:=True
       sText = ""
       For i = 1 To lRow
         sText = sText & .Cells(i, 1).Text
         .Cells(i, 1).Value = ""
       Next i
       rngSource.Value = sText
     End With
   Else
     With SpellCheck.Cells(1, 1)
       .Value = rngSource.Value
       .CheckSpelling IgnoreUppercase:=False, AlwaysSuggest:=True
       rngSource.Value = .Text
       .Value = ""
     End With
   End If
   
End Sub

Notes:
1. I changed the Codename of the worksheet to "SpellCheck" and reference that directly in the procedure.
2. The 500 value for MAXCHARS is somewhat arbitrary, but is comfortably below the 910 character limit.


Regards,
Mike

 

[mileslowe]

Thanks Mike, I will try that code out after running to a medical appointment. If that works, then I will try and finish up my project. I want to look at sheet1 and double click a cell to have it spell checked (this is what I am intending to do). I tried to modify the code so far to work by right clicking sheet1 tab and choosing code and pasted the following code in that code area. Here is the code pasted:

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)

Cancel = True   'Get out of edit mode

Dim strSpellCheckXtra As String
Dim iResponse As String
  
  If Target.Column <> 1 Then Exit Sub
  
  MsgBox "You could run a macro here " & Chr(10) & "you poked cell " & Target.Address(0, 0) & Chr(10) & _
    "in Row " & Target.Row & ", Column " & Target.Column & "The cell two columns to the right displays:  " & _
        Chr(10) & Target.Offset(0, 2).Text
        
    Range("A3").Select
    Selection.Copy
    Sheets("SpellCheck").Select
    ActiveSheet.Range("A1").Select
    ActiveSheet.Paste
  
If SpellingIsCorrect(tbxCellValue.Text) Then
      frmEditValues.BackColor = &HFF00&
    Else
    
    iResponse = MsgBox("There were spelling errors found, do you want to continue?  ", _
        vbYesNoCancel + vbQuestion + vbApplicationModal + vbDefaultButton1, "")

    Select Case iResponse
        Case vbYes:
            'NOT OK - Copy text data to hidden XLA SpellCheck worksheet
            frmEditValues.BackColor = &HFF&
            strSpellCheckXtra = tbxCellValue.Text
            ThisWorkbook.Worksheets("SpellCheck").Cells(1, 1).Value = strSpellCheckXtra
            'Now Check spelling
            Sheets("SpellCheck").Cells(1, 1).CheckSpelling AlwaysSuggest:=True
            frmEditValues.tbxCellValue.Value = ThisWorkbook.Worksheets("SpellCheck").Cells(1, 1).Value
            frmEditValues.BackColor = &HFF00&
          
    End Select
      
End If
  
End Sub

I then ran my project and ended up with an error runtime error 424, object required. There is something wrong with working with the code from the sheet level then by the worksheet level. I double click a cell with text and get the above error at the following code line:

If SpellingIsCorrect(tbxCellValue.Text) Then

hope your evening goes well!

 

[mileslowe]

Mike, the last posting I did may not be a good idea especially if the code has to be rewritten for sheets vs worksheet. I will have to find a way of running the code as modified by yourself from a sheet and return the results to the same cell as it was taken from for the textbox on the user form. What I need to do is select a cell from a sheet (by double clicking it etc.) which would load the text from the cell (keep track of the cell it came from so final text can be pasted back to this same cell) and have the cell's text pasted into the userform text box for modifiction and/or spell checking and the results returned to the same cell it came from on the sheet it came from. The userform is being used because the code bar expands when you click into a cell full of text and gets in the say of modifying the text and also the need to spell check the cell. Not sure if this makes sense or not. If yhou have any ideas as to how this might work, please get back to this post - thanks

 

[rmikesmith]

A couple of points here:

1. As you recognize, selecting a cell containing a substantial quantity of text can cause the formula bar area to easily obscure the cell. This makes it impossible to double-click. Obviously, the Userform approach doesn't help there, since double-clicking is the trigger. If you want use the approach you outlined, you just about have to hide the formula bar.

2.

Mike, the last posting I did may not be a good idea especially if the code has to be rewritten for [highlight]sheets vs worksheet[/highlight].

I'm not sure what you mean here. Are you talking about a generalized approach that will work on whatever worksheet is displayed? If so, then yes, you would want to use application-level events (e.g., SheetBeforeDoubleClick), which aren't as straightforward to implement as workbook-level events.


Regards,
Mike

 

[mileslowe]

Mike, thanks for the information. I am still unable to find a command in vb that would hide the formula bar. Here are my issues at present:

1. VBA code to hide and unhide the formula bar
2. VBA code to return the sheet name selected and cell address. I have been trying to do this with the following code but unsuccessfully

If Target.Column <> 1 Then Exit Sub
  
MsgBox "You could run a macro here " & Chr(10) & "you poked cell " & Target.Address(0, 0) & Chr(10) & _
    "in Row " & Target.Row & ", Column " & Target.Column & "The cell two columns to the right displays:  " & _
        Chr(10) & Target.Offset(0, 2).Text

The target address zero, zero part works with a textbox but I cannot get it to work within code to save the location to a string value to return the spell check'd value to the same sheet and cell when spell checking is done. Because I am a beginner as to coding or have done very little the concepts I want to use may not be the best.

3. I want to select a cell of medical notes for my mom's appointments (double clicking is the only way I know of that might work for this and now understand I must hide the address bar first as it gets in the way but not sure how to hide it and then unhide it when done) and copy text to the userform text box where I can add/delete text as needed for editing and spell check and finally return the text to the same orgininal cell by copying the corrected text from the userform text box.

4. I have been working all weekend trying to do this but have not beer real successful. I will also want to incorporate your new code for spell checking by splitting and assume that I would need to put your code as a function like the orginal code was used for the spell checking process. In order for that to work, I would have to call to the function some how from the userform ok click button.

 

[mileslowe]

I just found the commands needed to hid and unhide the address bar:

Application.DisplayFormulaBar = False 'Hide address bar
Application.DisplayFormulaBar = True 'Unhide address bar

 

[rmikesmith]

Turn the formula bar off when you open your workbook then on again when it's closed:

Private Sub Workbook_Open()
   Application.DisplayFormulaBar = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Application.DisplayFormulaBar = True
End Sub

These event handlers must appear in the ThisWorkbook module.
Note, this is not the ideal solution but is simple. If you open other workbooks while this one is open, the formula bar will still be hidden (since this is an application property, not a workbook property).

I mocked up your approach over the weekend. To track the cell being checked, I passed Target to the Userform (in a public variable; quick and dirty). When the user accepts changes, the textbox text can be assigned directly to the Value property. In other words, I'm working directly with a Range object, not a string representation of the cell address.


Regards,
Mike