Modification of Spell Check Dialog Box

[yeungsprite]

Hi,

I am wondering if anyone can direct me to some help regarding the use of the Spell Check Dialog Box. I would like to access Microsoft Excel's spell checking tool, but instead of clicking 'ok' to change words, I want to modify it so that a list of words can be separated into two lists of 'acceptable' and 'unacceptable' words.

I am familiar with the CheckSpelling calls, and am assuming that the code will be somewhat like the following. It will spell check column 'A' words, and if the user accepts the misspelled word, it will be moved to column 'E':
Dim xlApp As Excel.Application 'Excel-object
Set xlApp = New Excel.Application
xlApp.Application.Visible = visibleFiles
Dim aRange As Range ' Column in which to read values from
Dim i As Range ' value of the current cell
Dim icounter As Integer ' keeps track of cell to move incorrect words to

icounter = 1

Set aRange = xlApp.Range("A1", xlApp.ActiveSheet.Cells.SpecialCells(xlLastCell))
For Each i In aRange

' chk for end of range
If i = "" Then
Exit For
End If

' true if word is incorrect
If Not Application.CheckSpelling(i.Value, IgnoreUppercase:=False) Then

''add code here. ie: if user clicks 'Accept' then

' move word into new column
xlApp.ActiveCell.Select
i.Cut
i.Copy
xlApp.Range("E" & icounter).Select
xlApp.ActiveSheet.Paste
i.Select
i.Delete Shift:=xlUp
icounter = icounter + 1
End if
End If
Next i

Thank you for your help! it is greatly appreciated! The reason why I do not want to create my own spell check dialog is because the unicode text file problems in a manually created dialog box (the files which I am reading in Excel are unicode language files which are not necessarily English).

 

[rmikesmith]

yeungsprite,

I am trying to understand exactly what it is you want to achieve, but I am unclear on this. I want to point out a few things. First, Excel's (MS Office, actually) spell check dialog offers the following options: Ignore, Ignore All, Change, Change All, and Add. There is no OK option. Did you mean Change? Second, I don't believe there is any way to alter a built-in dialog box. Third, your use of the Application object qualifier for the CheckSpelling method does not display the spell check dialog box, just returns True/False.

Ok, all that being said, if I understand you correctly, you want to move a misspelled word to a new column (the "Acceptable" column?) rather than changing it. How does the user's choice come into play? Need some clarification here. From your code and comments, it appears the word is moved only if the user "accepts" it. What happens (or should happen) if the user chooses not to accept?

I think you can achieve what you want using the Application.CheckSpelling version in conjunction with your own dialog box (Userform). Your dialog would simply serve to display the word in question and get the user's decision (via a button or two) and, therefore, unicode would not be an issue, as it would be handled through the built-in spell check routine.

Post back with clarification and I'll try to help.

Regards,
Mike

 

[yeungsprite]

Hi Mike,
Thanks for the reply and I'll try to clarify my problem. I am importing a unicode(to support multiple languages) text file into Excel. I would like to run the spell check function on the list of words. This list of words will be all 'misspelled' words, but the spell check dialog box will just be used to identify each word, and allow the user to move it into an 'acceptable word' column, or leave it in its original column (since some correct words will not be in the dictionary). Basically I am parsing a list of failed words into an 'unacceptable' and 'acceptable' list.

The reason why I am hesitant to manually create a Userform is because MS Visual Basic 6.0 does not seem to be able to display Unicode Text words correctly. MS VS .Net does support Unicode, but previous versions do not fully support it, as far as I know. That is why I am relying on the built-in dialog box, because it is able to read unicode words when unicode text files are imported into Excel.

Thanks again,
Andrew

 

[rmikesmith]

Andrew,

I must admit I'm a little out of my depth here. Specifically, I have not worked directly with Unicode and don't use VB proper but rather, VBA. According to a reference book I have, beginning with VBA 5 (version used in Office 97), all strings are handled internally as Unicode. I would think this applies to VB also, so it seems odd that it has problems displaying Unicode. I ran across a function that may help, assuming it is available in VB; the StrConv function. Usage is

strOutput = StrConv(strInput,intConversion)

where one of the intConversion constants is vbFromUnicode. This will convert a Unicode string to ANSI. Try this out by assigning the converted string to a Textbox on a Userform. If this works (i.e. the text is displayed properly) then I think we could put something together to do what you want. Post back with your results.

Perhaps someone else on this forum will have other suggestions.

Regards,
Mike

 

[yeungsprite]

Thanks for the prompt reply. I am trying to follow your suggestion with the following simple code:

Sub Macro1()
Dim aRange As Range ' Column to run Spell Check on
Dim word As Range ' value of current cell

Dim convertedword As String

Set aRange = Range("A:A&quot

For Each word In aRange
If word = "" Then
Exit For
End If

convertedword = StrConv(word, vbFromUnicode)

MsgBox "Do you want to add the word: '" & convertedword & "' to the dictionary?", vbYesNoCancel, "Add Word?"
Next word

End Sub

The 'convertedword' value is not being displayed correctly in the msgbox. If I do not use the 'StrConv' function, the data will display correctly ONLY for some English words, but will display '??????' characters for other unicode text (ie russian, japanese, arabic).

 

[rmikesmith]

Andrew,

I don't know what is happening here. Perhaps you could send me a copy of a workbook containing some Unicode words and I'll play around with this. My email is:

rmikesmith@earthlink.net

Regards,
Mike