Excel VBA UserForm - SpellChecking - Text Frames 2

[hafod]

Hi,
The following code is called up on the click event of a 'spellcheck' button and relates to one of three text frames on a Data Entry/Validation User Form. Problem is, it will correctly identify a spelling error but is incapable of highlighting actual spelling error(s) in the frame in focus, or bring up standard Windows spell check dialog for subsequent correction. Can this be done programatically? The User Form (modal)is designed to prevents users from directly accessing raw Excel (database) data / and other features.

Hopy you can help

Hafod

Private Sub cmdSpellXtra_Click()
'Check HoY Spelling
frmDataEntryFormTutor.txtXtra.SetFocus
If (Application.CheckSpelling(txtHoY.Text) = True) Then
MsgBox ("spelling OK")
Else
MsgBox ("spelling error")
Columns("J:J").Select

'Selection.CheckSpelling SpellLang:=2057
End If
End Sub

 

[rmikesmith]

Hafod,

Here's a suggestion based on what I've done in a similar situation:

Create a hidden worksheet (preferably, set the worksheet's Visible property to xlVeryHidden). If you detect a spelling error using your code above, copy the text to a cell on the hidden worksheet (A1 for example). Execute the CheckSpelling method of the worksheet object:

Worksheets("SpellCheckSheet").CheckSpelling AlwaysSuggest:=True

This will bring up the familiar spell check dialog. Next, return the corrected text from the hidden sheet to the TextBox or directly to the data store, as desired.

Regards,
Mike

 

[hafod]

Hi Mike
Thanks for response. Attempting to implement your solution. Can programatically create temporary worksheet using Workbook_Open event. Having trouble deleting same on Workbook_BeforeClose. Can you help on this one before I try the actual spellchecking code you suggest. Tried various syntax options using Excel' s object model. I do not want my solution to attach and save the temp workssheet.
Thanks in anticipation.
Regards
Hafod - Mike

 

[rmikesmith]

Hafod,

Not sure why you don't want the hidden worksheet as part of your workbook. This would certainly be the simplest way to go. Here is code to remove the worksheet, then I'll comment on the problems with this approach:

Application.DisplayAlerts = False
ThisWorkbook.Worksheets("SpellCheck").Delete
Application.DisplayAlerts = True

This is placed in the Workbook_BeforeClose event procedure. The DisplayAlerts = False line prevent the delete sheet confirmation dialog from displaying. The problem is that if the workbook is saved by the user then the "temporary" worksheet is also saved. On next opening, a new worksheet will be created in addition to the desired temporary one, causing an error when the code attempts to give it the same name (which can be suppressed with an on error resume next statement). So, you would need additional code to check for the presence of this worksheet before creating a new one. If, on the other hand, the user never needs to save the workbook, then the creation of the temporary worksheet will still cause Excel to prompt a save... you'd need an additional line of code to handle that. It just seems much simpler to maintain a hidden worksheet that you create manually once!

Regards,
Mike

 

[hafod]

hi Mike
Again thanks for your response. Sorry if I implied I didnt want a 'hidden' worksheet - I did. In factI had set the hidden property as follows:

SpellCheckWks.Visible.xlSheetVeryHidden method

I now see your point of making this worksheet 'permanent' -within the AddIn workbook which makes the overall solution much more elegant than the temporary one that I inferred from your proposal. I will now follow this tack and let you know the outcome.

Incidentally, I did have problems with duplicate worksheets as your post suggests which again reinforces your simpler idea.
Again, many thanks,

Hafod (Mike)

 

[hafod]

Hi Mike,
Recall, my VBA application is an Data Entry Excel Add-In with various GUI UserForms and supporting project modules. Users will intall the Add-In giving them access to the User Forms via sub-menus (all these work fine). Now users will import a partly locked single worksheet from a third-party system for data updating. This is always exported from the 3rd party sytem as 'Sheet1' and has to be imported as same name. Changes made to 'sheet1' (including text validation/spellchecking) are then subsequently saved for import into this management system.

Now, following your approach I need to embed the 'hidden sheet' as part of the Add-In (xla) I developed. However, having difficulty viewing any Add-In Spreadsheet objects displayed in the VBA IDE to make one, the renamed spellcheck worksheet 'hidden'. The Project MsExcel Objects named in the VB IDE are - 'SpellCheckWks', 'Sheet1' and 'Sheet2'along with UserForms and Modules.

I have looked at ways of making 'SpellCheckWks' visible (initially for test purposes) when switching to Excel but to no avail. I have tried same with a new blank workbook saving it as an xla but this gives same results. Am I missing something fundamental here with the use of xla's?

Hope you can help me out here.
Best regards,
Mike

 

[rmikesmith]

Mike,

Didn't realize your app was an Add-In. An Excel Add-In never displays worksheets so there is no need to make it 'hidden'. If you wish to view worksheets during development do the following:
[ul]
[li]In the VBE select the ThisWorkbook object[/li]
[li]Ensure that the Properties window is displayed (if not, select View|Properties Window from the VBE menu)[/li]
[li]Scroll down to the IsAddin property and change it to False[/li]
[/ul]
You will now be able to view any worksheets. Change the IsAddin property back to True when done.

Regards,
Mike

 

[hafod]

Mike,
Many thanks for your last post - resolves that issue. Finally, could you please look at the code below. It relates to a 'Spell Check' button alongside the UserForm text frame in the AddIn xla. Command Button Colour changing works fine depending upon spelling status.

However I get an 'Error 9 - Subscript out of range' error at Line *** below. The string variable which contains the copy text is in fact populated. I suspect it is due to the fact that 'Sheet1 s' workbook has 'focus' in project - not the xla AddIn 'SpellCheck' worksheet related to the imported files 'project'.

I am not sure how Excel' s object model can be used to explicity reference 'Sheet1'. Incidentally, what method would you use to return corrected text from 'sheet1 - R1, C1' to 'spellckeck'. This WILL complete the application. (So close!) Any suggestions Mike?


Private Sub cmdSpellXtra_Click()
'Check HoY Spelling
'OK
If (Application.CheckSpelling(txtXtra.Text) = True) Then
cmdSpellXtra.BackColor = &HFF00&
Else
'NOT OK - Copy text data to hidden XLA 'SpellCheck' worksheet
cmdSpellXtra.BackColor = &HFF&
strSpellCheckXtra = txtXtra.Text
(****) Application.Worksheets("SpellCheck").Cells(1, 1).Value = strSpellCheckXtra
'Now Check spelling
Worksheets("SpellCheck").CheckSpeling AlwaysSuggest:=True
'Selection.CheckSpelling SpellLang:=2057
End If
End Sub

Again, many thanks for your helpful information. It has been invaluable in filling in gaps in my Excvel VBA knowledge.

Best regards,
Mike

 

[rmikesmith]

Mike, you're quite welcome.

To correct the "subscript out of range" error, replace Application with ThisWorkbook in the line of code causing the error. In this context, Application (Excel) doesn't 'see' the Add-In's worksheets.

I'm not sure I understand the flow of data from your question:

Incidentally, what method would you use to return corrected text from 'sheet1 - R1, C1' to 'spellckeck'.

Assuming you want to return corrected text from the SpellCheck worksheet (Add-In) to Sheet1 in the current open workbook, try something like:

ActiveWorkbook.ActiveSheet.Cells(1, 1).Value = ThisWorkbook.Worksheets("SpellCheck").Cells(1,1).Text

Regards,
Mike

 

[hafod]

Hi Mike,
Thanks for last response Mike. (Will try it 'soon'). With regard to the context of my question, I meant what event triggers the subsequently corrected text (in 'SpellCheck') being transferred to the Userform text frame. Your proposed code will do the transfer (to be implemented), but what triggers this code.

For example, the command button initiates text transfer FROM UserForm TextFrame to the Hidden SpellCheck sheet and enables a text correction facility for the text string in that cell A1. Then, something must initiate the copying of text data FROM this cell (spellchecked text) BACK to the UserForm text frame. I hope I have clarified this adequately.

Regards,
Mike

 

[rmikesmith]

Mike,

Yes, I get it now. Since your code will halt temporarily while the Spell Check dialog box is open you can add code to move the corrected text back to the Userform directly after the line that invokes the dialog box. Example, with added line bolded:

Private Sub cmdSpellXtra_Click()
'Check HoY Spelling
   'OK
    If (Application.CheckSpelling(txtXtra.Text) = True) Then
        cmdSpellXtra.BackColor = &HFF00&
      Else
         'NOT OK - Copy text data to hidden XLA 'SpellCheck' worksheet
          cmdSpellXtra.BackColor = &HFF&
              strSpellCheckXtra = txtXtra.Text
                    (****) Application.Worksheets("SpellCheck").Cells(1, 1).Value = strSpellCheckXtra
                    'Now Check spelling
                    Worksheets("SpellCheck").CheckSpelling AlwaysSuggest:=True
                    [b]txtXtra.Text = ThisWorkbook.Worksheets("SpellCheck").Cells(1,1).Text[/b]
    End If
End Sub

Regards,
Mike

 

[hafod]

Mike,
Thanks for that. Will try code over weekend. Have a good weekend. Thanks for all your help. Will acknowledge Sunday/Monday.

Best regards,
Mike

 

[hafod]

Hi Mike
Sorry for delay - but busy few days! The revised code works a treat and I have extended its use further. Two further interesting points emerge in relation to this topic to 'embelish' the solution:

(i) Is it possible to grammar-check text in excel? I can see no references in the object library. Realistically, I would expect this feature perhaps to be more akin to WP/dtp and not Excel. Shelling to MS Word to achieve this, if indeed this were practicable would not, I feel be elegant.

(II) This one is more pressing. A prefered additional feature would be the ability of users to highligh selected text in the text box - not necessarily all the text. Then, COPY and paste the selection into the cursor location in the associated text box for other records (ie traditional 'MS Word COPY-PASTE' approach). Is this possible?. Copying and pasting 'en-block' text is not a problem and I have implemented this feature for say, 'student targets'.

I have really appreciated your professional help on this project.
Best regards,
Mike (Hafod)

 

[rmikesmith]

Mike,

To answer your questions:

i) As far as I know, there is no method to check grammar exposed via the Excel object model. If this is needed you could use Word. However, you would not have to "shell out" as Word can be accessed/controlled directly from within Excel. It is possible to do this and have Word do its thing behind the scenes (not visible). Of course, this would make the whole process somewhat more complex and it does take a small amount of extra time to access Word through Automation. I've got some sample code if interested.

ii) Copy & Paste is already built-in to the TextBox control. Simply highlight the desired text in TextBox1, press Ctrl-C, click into TextBox2 where you want to insert the text, then press Ctrl-V.


Regards,
Mike

 

[hafod]

Hi Mike
Yes, with regard to Ctrl-V and Ctrl-C you stated the obvious - I should have been aware of these key sequences. I agree with you regarding automating application process - this will overcomplicate things and is, I feel an unescessary overkill.

As part of my program debugging you may also be interested with following text string which gives rise to a trappable error when the spell chech is initiated:
This is typical and OK:
"Jessica is a member of the school Wind Band, playing the Flute and Clarinet. She is also a member of the School Choir and takes part in a number of Competitions during the year."

When this string is appended with a random character string say

"oiiooo ooiooi ioiio iiu iioioi" a 'Type mismatch error' results. This is suprising because the text string is placed in a string variable and the error is consistent.

I initially used these arbitary characters (non numeric)to quickly check line count and character count validation routines in the program. Is this a bug in the Windows spelling dll or could it be my coding? Having said that, the degree of coding is relatively straightforward, based upon your proposal and your solution works consistently well excepting these, perhaps extreme situations.
Best regards
Mike

 

[rmikesmith]

Mike,

Post your string concatenation code.


Regards,
Mike

 

[hafod]

Hi Mike,
Thanks for your reply. Perhaps my post was a little confusing. I did not 'programatically concatenate' the two strings, I just appended the 'second' string (cut/paste)- to the first in the text box itself prior to performing the spell check. It is these additional 'padding, alpha characters of this form which can give rise to Type Mismatch errors. Whilst these clearly are an extreme (except perhaps for the less literate of staff!) it does bring into question perhaps the robustness of the spellchecker because there is no code of mine involved in this process. Have you encountered similar problems?

"Jessica is a member of the school Wind Band, playing the Flute and Clarinet. She is also a member of the School Choir and takes part in a number of Competitions during the year oiiooo ooiooi ioiio iiu iioioi" .

Best Regards,
Mike

 

[rmikesmith]

Mike,

I have been unable to duplicate this problem. Can you post the code (entire procedure) and indicate the line where the Type Mismatch error occurs?


Regards,
Mike

 

[hafod]

Hi Mike,
Offending line of code shown thus: *****

code
Private Sub cmdSpellXtra_Click()
'On Error GoTo Err_Handler
'Check HoY Spelling
'OK
*******If (Application.CheckSpelling(txtXtra.Text) = True) Then
cmdSpellXtra.BackColor = &HFF00&
Else
'NOT OK - Copy text data to hidden XLA SpellCheck worksheet
'See Module 1 Notes to Uhide xla Worksheet temporarily
cmdSpellXtra.BackColor = &HFF&
strSpellCheckXtra = txtXtra.Text
ThisWorkbook.Worksheets("SpellCheck").Cells(1, 1).Value = strSpellCheckXtra
'Now Check spelling
ThisWorkbook.Worksheets("SpellCheck").CheckSpelling AlwaysSuggest:=True
txtXtra.Text = ThisWorkbook.Worksheets("SpellCheck").Cells(1, 1).Text
cmdSpellXtra.BackColor = &HFF00&


End If

Exit Sub
Err_Handler:
Message = "Error Message / Number: "
Message = Message & Err.Description & vbCr & vbCr
Message = Message & "Please note Error Message and report" & vbCr
Message = Message & "it to M Harries together with circumstances which gave" & vbCr
Message = Message & "rise to it. Thank you"
Message = Message & vbCr
Message = Message & "M Harries, Head of ICT"

Answer = MsgBox(Message, vbOKOnly, "Error Message")
'
If Answer = vbOK Then
Unload frmDataEntryFormTutor
Exit Sub
End If

End Sub

Since last post I have temporarily disabled this line and control loop and problem seems to be resolved irrespective of character combination used. However I can no longer use control loop to give user status messages based on boolean state of spelling method.

Incidentally, can you remind me of programmatic short cut for say Ctr+C (or V)- do I need Ascii code chars? Will then attach code to associated command buttons.

Again Best Regards,
Mike


/code

 

[rmikesmith]

Mike,

Sorry to say, but I still cannot duplicate the error condition. I believe I am reproducing your situation but that may not be the case as it is difficult to determine remotely.

Just to be clear... You have a Userform containing one or more TextBox controls (e.g. txtXtra) and a CommandButton to run the spell check code. You say you cut/pasted the extraneous (dummy) text into the TextBox. What was the source? What happens if you simply type gibberish at the end of the TextBox text? I have done this both ways without an error.

The particular error you are getting, "Type Mismatch", is odd in this context and should be telling us something.

As to your other question, here are two example procedures you can use with command buttons on the Userform:

Private Sub cmdCopy_Click()
  With TextBox1
    If .SelLength > 0 Then
      .Copy
    End If
  End With
End Sub

Private Sub cmdPaste_Click()

   If Application.ClipboardFormats(1) = xlClipboardFormatText Then
     TextBox2.Paste
   Else
     MsgBox "There is no text to paste."
   End If
End Sub

Regards,
Mike