Performing a spell check on the contents of a userform field (EXCEL) (1 Viewer)

[Rabbitoh]

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.

 

[Rich]

RunCommand acCmdSpelling

 

[Rabbitoh]

RunCommand acCmdSpelling

How exactly would I use this code in the module please (novice)?

 

[icezebra]

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:

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

 

[Rabbitoh]

My Form does not show an event tab. Tried the code without but could not get to work. Thanks anyway.

For the benefit of others, I did it this way thanks to Andy Pope.

Range("A1") = TextBox1.Text
Range("A1").CheckSpelling

then unloaded the Form and reloaded it again with the updated cell content (text now corrected by the Excel Spellchecker)

 

[boblarson]

My Form does not show an event tab. Tried the code without but could not get to work. Thanks anyway.

For the benefit of others, I did it this way thanks to Andy Pope.

Range("A1") = TextBox1.Text
Range("A1").CheckSpelling

then unloaded the Form and reloaded it again with the updated cell content (text now corrected by the Excel Spellchecker)

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.

 

[GaryPanic]

well never mind, I need this for myself (espically with my spelling)- so it hasn't gone to waste .

g

 

[taranto]

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.

Any thoughts on this please

 

[DCrake]

Re: Performing a spell check on the contents of a userform field

The spell checker settings can set to ignore words in upper case.

 

[taranto]

Re: Performing a spell check on the contents of a userform field

Thanks for your time David, I couldn't see the wood for the trees !

 

[PatrickOfLondon]

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

 

[PatrickOfLondon]

Re: Performing a spell check on the contents of a userform field

... and to make double-sure, you can also add an Event procedure to spell-check the form-field when the user clicks out of it:

Private Sub YourFieldName_Exit(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSpelling
DoCmd.SetWarnings True
End Sub

 

[ct2013]

Re: Performing a spell check on the contents of a userform field

Would there be a way like in word it automatically comes up as you type to do the same in access.

also i used this code "DoCmd.RunCommand acCmdSpelling"

how would i exclude a certain field in the check from my form i.e. i want to check 5 fields but not the last one.

Ta

 

[PatrickOfLondon]

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.

 

[ct2013]

Re: Performing a spell check on the contents of a userform field

I used this code.

DoCmd.RunCommand acCmdSpelling

Was wondering how to create a code that afterupdate make words with if there is a spellcheck error, red font colour or underline.....

If anyone could help i would be most grateful.

Ta

 

[NoSmoke]

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

 

[ashleedawg]

Re: Performing a spell check on the contents of a userform field

Next time you might want to specify that you were doing this in EXCEL and not Access

It would indeed be handy if the title of this question was renamed in indicate it's an Excel question.

That said, another way to check spelling in Access is with the help of a reference to Excel.

From a related answer of mine:

Using Excel's VBA Functions in MS Access:

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:

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

 

[isladogs]

Welcome back ashleedawg. Long time no see.
Title change made as requested.