I want to run a spell check on the contents entered by a user into a field on an active Form. This can be either automatic as user types or manually activated via a button on the Form itself. Can this be done and if so, what code would allow this to take place.
Open the form for editing, right click on the field you wish to spell check - go to properties - then to the "Event" tab. Double click the "On_Exit" box - [Event Procedure] will appear. Click the box with three dots inside that appears to the right. The VB Editor will appear.
Use the below code:
Code:
Private Sub YOURFIELDNAME_Exit(Cancel As Integer)
Dim strSpell
strSpell = YOURFIELDNAME
If IsNull(Len(strSpell)) Or Len(strSpell) = 0 Then
Exit Sub
End If
With YOURFIELDNAME
.SetFocus
.SelStart = 0
.SelLength = Len(strSpell)
End With
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSpelling
DoCmd.SetWarnings True
End Sub
Next time you might want to specify that you were doing this in EXCEL and not Access. The Modules and VBA category is normally for ACCESS questions and we have an Excel category.
Re: Performing a spell check on the contents of a userform field
I really love this spell check feature and have introduced it sucessfully using the code shown earlier in this thread. Thanks for that.
The only thing is that if I use lower case in the text field or an upper case as the first letter followed by lower case it works. If I have caps lock on or type words in upper case they slip through without been shown as a spelling error.
Re: Performing a spell check on the contents of a userform field
Hi to everyone above, and anyone else looking for an answer to this.
Here's another option you can try, which I alighted on after experimenting with some of the above suggestions, when wanting to add a spell check feature to an Access 2010 form field:
* from design view in the form concerned, right-click in the field you want to spell-check
* choose: Properties - to display the property sheet if it's not already on display. (If it is this will turn it off!)
* in the Property sheet, click the Event tab
* for the On Dbl Click event, click the three dots at the far right of that row
* enter this code, between the "Private Sub..." and "End Sub" lines now created for the event:
' DoCmd.SetWarnings False ' optional; remove the first ' in this line and its counterpart below if you want to use them DoCmd.RunCommand acCmdSpelling
' DoCmd.SetWarnings True ' optional; remove the first ' in this line and its counterpart above if you want to use them
' Msgbox "Spelling checked" ' remove the first ' in this line, if you use the SetWarnings lines above
Return to normal Form view for your form. If you now double-click within the relevant form field, the spelling will be checked. If you wish you could add a text label alongside or above the field, e.g. "Double-click within this field to spell-check it".
Re: Performing a spell check on the contents of a userform field
I don't know of any way to establish as-you-type spell-checking in MS Access, which seems to be the "poor relation" in MS Office for spell-checking.
The VBA spell-checking method described above is already on a field-by-field basis, so you need to add the corresponding code in an Event Procedure for each of the fields you want to spell-check.
Re: Performing a spell check on the contents of a userform field
Have been experimenting with the spell checker with text in text boxes. It seems to work pretty well but have noticed a couple of quirks.
First is when the spell checker is invoked (due to a spelling error), the cursor in that text box, and the highlighting of erroneous spelling, sometimes becomes misplaced about 2 character widths to the right. This is sometimes persistent even if the text box is exited and returned to (which makes any subsequent editing messy).
The second is the option to automatically capitalize the first word of a sentence works only if the sentence is preceded by a "." IOWs, the auto capitalization does not work for the first sentence in the box.
Was wondering if anyone else has noticed this and if there are any solutions...
You can call Excel functions from Access by adding a reference to Excel.
In the Access VBA Project, go Tools → References, and then locate and include the library "Microsoft Excel 16.0 Object Library" (or the newest version you have).
Then create an object referring to an Excel Application and call whatever commands as required.
For example, to check the spelling of a word or phrase:
Code:
Sub demo_Spellcheck()
Dim textToCheck As String, excel As New excel.Application 'create Excel object
textToCheck = InputBox("Enter a word or phrase:", "Spellcheck", "blah blah")
If excel.Application.CheckSpelling(textToCheck) Then 'check spelling
MsgBox textToCheck & vbLf & "is spelled Correctly", 64 'TRUE = correct
Else
MsgBox textToCheck & vbLf & "is mis-spelled", 16 'FALSE = misspelled
End If
Set excel = Nothing 'always cleanup after your objects
End Sub
