Change Event.....Spell Check

[CLR]

Hi All..............

I am trying to create/record a macro that will automatically start the Spell
Checker on a cell as soon as data is entered into it.

I have a large cell created by merging many cells (A63:g69) to form a NOTES:
box at the bottom on one of my sheets. Many of my users can't spell, and I
would like the SpellChecker to pop up as soon as they hit Enter after typing
in that cell, but would like to restrict it to only checking THAT one
cell-range,.............right now it wants to check the whole sheet, even
the cells in hidden columns..........

Here's what I've got so far...........

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$63" Then
Range("A63:G69").Select
Cells.CheckSpelling CustomDictionary:="CUSTOM.DIC",
IgnoreUppercase:=False _
, AlwaysSuggest:=True, SpellLang:=1033
ActiveWindow.ScrollColumn = 1
Application.EnableEvents = True
End If
End Sub

Any help would be appreciated..........

Vaya con Dios,
Chuck, CABGx3

 

[Doug Glancy]

In your code the word Cells in "Cells.CheckSpelling" tells it to check the
whole sheet, regardless of your previous selection. Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A63:G69")) Is Nothing Then
Range("A63:G69").CheckSpelling CustomDictionary:="CUSTOM.DIC",
IgnoreUppercase:=False, _
AlwaysSuggest:=True, SpellLang:=1033
ActiveWindow.ScrollColumn = 1
End If

End Sub

hth,

Doug

 

[CLR]

OUTSTANDING Doug...........thanks muchly.
It's beautiful............exactly what I wanted.

Oh yeah, is there any way to qualify it so it will only run if the character
count in the cell/range is less than 257?

Vaya con Dios,
Chuck, CABGx3

 

[Doug Glancy]

Chuck,

This seems to work, although, note that if it's a string with no spaces it
only works if it's under 255 characters. But if there are spaces in the
textbox contents, then it does what you asked for:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A63:G69")) Is Nothing And Len(Range("A63"))
<= 256 Then
Range("A63:G69").CheckSpelling CustomDictionary:="CUSTOM.DIC",
IgnoreUppercase:=False, _
AlwaysSuggest:=True, SpellLang:=1033
ActiveWindow.ScrollColumn = 1
End If

End Sub

Doug

 

[CLR]

Hi Doug............

I been working on the other computer all evening.......it's got
problems.........and haven't had a chance to install your new code but from
the looks of it, thats exactly what I was looking for...........

Thanks again so very much............

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

I just got a chance to check out your latest code this morning Doug, and it
does work EXACTLY like I wanted it to..............many MANY
Thank-you's...........

Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

Just a question. When I tried this fix approach in xl97, the spell checker
did check the restricted range, but after finishing that, asked it I wanted
to check the remainder of the document. Does that not happen in your case
and what version of excel.

Thanks,
Tom Ogilvy

 

[Doug Glancy]

Tom,

You're right, it doesn't work with 97. It does in 2k. How would you do it?

Thanks,

Doug