Spell Check only Cell that has changed

**mowens74** · 06-12-2019, 03:31 AM

Hello,
I am trying to run a VBA to spell check a cell that has been changed and not the whole sheet. I have tried the following....

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = True
' ~~>  Spell check changed cells in column 'D'
    If Target.Column = 4 Then
        Application.EnableEvents = False
            If ActiveCell.Row > 2 Then
                For Each Cell In Intersect(Target, Columns(4))
                    Selection.CheckSpelling
                Next
            End If
    Application.EnableEvents = True
    End If
End Sub

....and....

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = True
' ~~>  Spell check changed cells in column 'D'
    If Target.Column = 4 Then
        Application.EnableEvents = False
            If ActiveCell.Row > 2 Then
                    Selection.CheckSpelling
            End If
    Application.EnableEvents = True
    End If
End Sub

....but both check the whole sheet. I have also tried 'Cell.CheckSpelling' instead of 'Selection.CheckSpelling' in both sections but same issue.

Thank you.

**Marc L** · 06-12-2019, 06:11 AM

Hi !

Try replacing in first code Activecell by Target and Selection by Cell …

**mowens74** · 06-12-2019, 06:41 AM

Marc L: Just to confirm, I have changed the code to....

    If Target.Column = 4 Then
        Application.EnableEvents = False
            If Target.Row > 2 Then
                For Each Cell In Intersect(Target, Columns(4))
                    Cell.CheckSpelling
                Next
            End If
    Application.EnableEvents = True
    End If

....but same issue, it checks the other cells.

**WideBoyDixon** · 06-12-2019, 06:42 AM

Private Sub Worksheet_Change(ByVal Target As Range)

Dim checkRange As Range
Dim checkCell As Range

Set checkRange = Application.Intersect(Target, Range("D:D"))
If checkRange Is Nothing Then Exit Sub

Application.EnableEvents = False
For Each checkCell In checkRange
    If checkCell.Row > 2 Then
        checkCell.CheckSpelling
    End If
Next checkCell
Application.EnableEvents = True

End Sub

WBD

**mowens74** · 06-12-2019, 08:19 AM

Thanks WideBoyDixon but it still checks the rest of the document.

**WideBoyDixon** · 06-12-2019, 08:43 AM

Ah. A little research suggests that checking spelling on a single cell will check the whole sheet. Use a fixed cell that doesn't contain spelling errors and do it like this:

Private Const CellWithNoErrors = "D1"
Private Sub Worksheet_Change(ByVal Target As Range)

Dim checkRange As Range
Dim checkCell As Range

Set checkRange = Application.Intersect(Target, Range("D:D"))
If checkRange Is Nothing Then Exit Sub

Debug.Print checkRange.Address

Application.EnableEvents = False
For Each checkCell In checkRange
    If checkCell.Row > 2 Then
        Application.Union(Range(CellWithNoErrors), checkCell).CheckSpelling
    End If
Next checkCell
Application.EnableEvents = True

End Sub

WBD

**mowens74** · 06-13-2019, 03:20 AM

WBD - thath works great, thank you.

Spell Check only Cell that has changed

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Spell Check only Cell that has changed

Re: Spell Check only Cell that has changed

Re: Spell Check only Cell that has changed

Re: Spell Check only Cell that has changed

Re: Spell Check only Cell that has changed

Re: Spell Check only Cell that has changed

Thread Information

Users Browsing this Thread

Similar Threads

Changing Spell Check language of Vba Spell Checker

How to detect Spell Check activated - or - exit sub when you press cancel on Spell Check

spell check changed cells

How can I protect a cell and keep the spell check working?

[SOLVED] Spell Check an Unlocked Cell in a Protected Spreadsheet

Spell Check an Unlocked Cell in a Protected Spreadsheet

[SOLVED] Spell Check an Unlocked Cell in a Protected Spreadsheet

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions