VBA Check Spelling doesn't highlight misspelled word

T

TWhizTom

I am spellchecking a range (based on the current active sheet name). The
range is multi-rowed, but single column. When user clicks check spelling, it
unprotects the sheet spell checks the range and re-protects the sheet.

The issue is with some of these ranges there are over 50 often 100 rows. A
user may not be able to figure out what their spelling mistake was because
the error is not highlighted, therefore, the context of the spelling error is
lost...

How can I activate the cell or highlight the misspelled word? Code Example
follows:

<code>
Sub Spelling()
QuickUnprotect
Application.EnableEvents = False
Select Case ActiveSheet.Name
Case "Cover"
Range("Cover_SpellCheck").CheckSpelling
Case "FacilityData"
Range("Facility_CheckSpell").CheckSpelling
Case "4.0"
Range("QSR_SpellCheck").CheckSpelling
strHidden = Range("QSR_AuditorComments").EntireColumn.Hidden
Range("QSR_AuditorComments").EntireColumn.Hidden = False
Range("QSR_AuditorComments").CheckSpelling
Range("QSR_AuditorComments").EntireColumn.Hidden = True
Range("QSR_AuditorComments").EntireColumn.Hidden = strHidden
Range("QSR_4_2_Comment").Select
Case "5.0"
Range("Mgmt_Spellcheck").CheckSpelling
strHidden = Range("Mgmt_AuditorComments").EntireColumn.Hidden
Range("Mgmt_AuditorComments").EntireColumn.Hidden = False
Range("Mgmt_AuditorComments").CheckSpelling
Range("Mgmt_AuditorComments").EntireColumn.Hidden = True
Range("Mgmt_AuditorComments").EntireColumn.Hidden = strHidden
Case "6.0"
Range("Res_Spellcheck").CheckSpelling
strHidden = Range("Res_AuditorComments").EntireColumn.Hidden
Range("Res_AuditorComments").EntireColumn.Hidden = False
Range("Res_AuditorComments").CheckSpelling
Range("Res_AuditorComments").EntireColumn.Hidden = True
Range("Res_AuditorComments").EntireColumn.Hidden = strHidden
Case "7.0"
Range("Prod_Spellcheck").CheckSpelling
strHidden = Range("Prod_AuditorComments").EntireColumn.Hidden
Range("Prod_AuditorComments").EntireColumn.Hidden = False
Range("Prod_AuditorComments").CheckSpelling
Range("Prod_AuditorComments").EntireColumn.Hidden = True
Range("Prod_AuditorComments").EntireColumn.Hidden = strHidden
Case "8.0"
Range("Meas_Spellcheck").CheckSpelling
strHidden = Range("Meas_AuditorComments").EntireColumn.Hidden
Range("Meas_AuditorComments").EntireColumn.Hidden = False
Range("Meas_AuditorComments").CheckSpelling
Range("Meas_AuditorComments").EntireColumn.Hidden = True
Range("Meas_AuditorComments").EntireColumn.Hidden = strHidden
Case "Summary"
Range("Sum_SpellCheck").CheckSpelling
End Select
QuickProtect
Application.EnableEvents = True
MsgBox "SpellCheck Complete", vbOKOnly, "Spelling"
End Sub
</code>
 
R

ryguy7272

If you care to correct errors as you go, either of these may work for you?

Sub SpellDown()
Do
ActiveCell.Offset(1, 0).Select
ActiveCell.CheckSpelling SpellLang:=1033, _
AlwaysSuggest:=True
On Error GoTo Right
Loop
Right:
ActiveCell.Offset(0, 1).Select
SpellUp
End Sub

Sub SpellUp()
Do
ActiveCell.Offset(-1, 0).Select
ActiveCell.CheckSpelling SpellLang:=1033, _
AlwaysSuggest:=True
On Error GoTo Right
Loop
Right:
ActiveCell.Offset(0, 1).Select
SpellDown
End Sub

HTH,
Ryan---
 
T

TWhizTom

Thanks Ryan,

However, I don't want to check the whole sheet, just the named ranges. I
have not tried your code, but it does not appear to check the named ranges
only.
 
R

ryguy7272

I don't have time to modify the code right now, and it would be a little
difficult for me to do so because I don't have all those named ranges that
you have. Anyway, I think the macro will do what you want, once you change
it to look at the named ranges you specified. Or, just run it as is. How
much more time could it possible take to check cells that are not within the
named ranges, assuming there aren't lots and lots of spelling mistakes to
begin with.

HTH,
Ryan---
 
T

TWhizTom

Thanks again ryan,

Do not expect you to rebuild it ( lol ). I will take a look at using your
code or modifying. But, is looking more and more like going to have to setup
a for next and loop through the range and copy if not hidden.... It appears
that the merged cells are causing the .specialcells(xlcelltypevisible) to not
work.

Checking the additional areas is not an option. There is not lot nor is time
the issue, but there are a lot of industrial terms, acronmyns, and
abbreviations that are going to pop the spell check box....

Thanks for trying!
 
R

r c

I have had this same problem, but have finally figured out that if you use .checkSpelling in vba then it will not update your screen while spell checking even if you have application.screenupdating = true.

In order to get around this you can invoke the spell checking command from the command bar. In one instance i needed to have Application.ScreenUpdating = True on and one i didn't, but having it on doesn't hurt anything most of the time..

Something like:

rangeToCheck.Select
Application.ScreenUpdating = True
Application.CommandBars("Tools").Controls("Spelling...").Execute



TWhizTo wrote:

VBA Check Spelling doesn't highlight misspelled word
16-Mar-09

I am spellchecking a range (based on the current active sheet name). The
range is multi-rowed, but single column. When user clicks check spelling, it
unprotects the sheet spell checks the range and re-protects the sheet

The issue is with some of these ranges there are over 50 often 100 rows. A
user may not be able to figure out what their spelling mistake was because
the error is not highlighted, therefore, the context of the spelling error is
lost..

How can I activate the cell or highlight the misspelled word? Code Example
follows

<code
Sub Spelling(
QuickUnprotec
Application.EnableEvents = Fals
Select Case ActiveSheet.Nam
Case "Cover
Range("Cover_SpellCheck").CheckSpellin
Case "FacilityData
Range("Facility_CheckSpell").CheckSpellin
Case "4.0
Range("QSR_SpellCheck").CheckSpellin
strHidden = Range("QSR_AuditorComments").EntireColumn.Hidde
Range("QSR_AuditorComments").EntireColumn.Hidden = Fals
Range("QSR_AuditorComments").CheckSpellin
Range("QSR_AuditorComments").EntireColumn.Hidden = Tru
Range("QSR_AuditorComments").EntireColumn.Hidden = strHidde
Range("QSR_4_2_Comment").Selec
Case "5.0
Range("Mgmt_Spellcheck").CheckSpellin
strHidden = Range("Mgmt_AuditorComments").EntireColumn.Hidde
Range("Mgmt_AuditorComments").EntireColumn.Hidden = Fals
Range("Mgmt_AuditorComments").CheckSpellin
Range("Mgmt_AuditorComments").EntireColumn.Hidden = Tru
Range("Mgmt_AuditorComments").EntireColumn.Hidden = strHidde
Case "6.0
Range("Res_Spellcheck").CheckSpellin
strHidden = Range("Res_AuditorComments").EntireColumn.Hidde
Range("Res_AuditorComments").EntireColumn.Hidden = Fals
Range("Res_AuditorComments").CheckSpellin
Range("Res_AuditorComments").EntireColumn.Hidden = Tru
Range("Res_AuditorComments").EntireColumn.Hidden = strHidde
Case "7.0
Range("Prod_Spellcheck").CheckSpellin
strHidden = Range("Prod_AuditorComments").EntireColumn.Hidde
Range("Prod_AuditorComments").EntireColumn.Hidden = Fals
Range("Prod_AuditorComments").CheckSpellin
Range("Prod_AuditorComments").EntireColumn.Hidden = Tru
Range("Prod_AuditorComments").EntireColumn.Hidden = strHidde
Case "8.0
Range("Meas_Spellcheck").CheckSpellin
strHidden = Range("Meas_AuditorComments").EntireColumn.Hidde
Range("Meas_AuditorComments").EntireColumn.Hidden = Fals
Range("Meas_AuditorComments").CheckSpellin
Range("Meas_AuditorComments").EntireColumn.Hidden = Tru
Range("Meas_AuditorComments").EntireColumn.Hidden = strHidde
Case "Summary
Range("Sum_SpellCheck").CheckSpellin
End Selec
QuickProtec
Application.EnableEvents = Tru
MsgBox "SpellCheck Complete", vbOKOnly, "Spelling
End Su
</code>

Previous Posts In This Thread:

VBA Check Spelling doesn't highlight misspelled word
I am spellchecking a range (based on the current active sheet name). The
range is multi-rowed, but single column. When user clicks check spelling, it
unprotects the sheet spell checks the range and re-protects the sheet.

The issue is with some of these ranges there are over 50 often 100 rows. A
user may not be able to figure out what their spelling mistake was because
the error is not highlighted, therefore, the context of the spelling error is
lost...

How can I activate the cell or highlight the misspelled word? Code Example
follows:

<code>
Sub Spelling()
QuickUnprotect
Application.EnableEvents = False
Select Case ActiveSheet.Name
Case "Cover"
Range("Cover_SpellCheck").CheckSpelling
Case "FacilityData"
Range("Facility_CheckSpell").CheckSpelling
Case "4.0"
Range("QSR_SpellCheck").CheckSpelling
strHidden = Range("QSR_AuditorComments").EntireColumn.Hidden
Range("QSR_AuditorComments").EntireColumn.Hidden = False
Range("QSR_AuditorComments").CheckSpelling
Range("QSR_AuditorComments").EntireColumn.Hidden = True
Range("QSR_AuditorComments").EntireColumn.Hidden = strHidden
Range("QSR_4_2_Comment").Select
Case "5.0"
Range("Mgmt_Spellcheck").CheckSpelling
strHidden = Range("Mgmt_AuditorComments").EntireColumn.Hidden
Range("Mgmt_AuditorComments").EntireColumn.Hidden = False
Range("Mgmt_AuditorComments").CheckSpelling
Range("Mgmt_AuditorComments").EntireColumn.Hidden = True
Range("Mgmt_AuditorComments").EntireColumn.Hidden = strHidden
Case "6.0"
Range("Res_Spellcheck").CheckSpelling
strHidden = Range("Res_AuditorComments").EntireColumn.Hidden
Range("Res_AuditorComments").EntireColumn.Hidden = False
Range("Res_AuditorComments").CheckSpelling
Range("Res_AuditorComments").EntireColumn.Hidden = True
Range("Res_AuditorComments").EntireColumn.Hidden = strHidden
Case "7.0"
Range("Prod_Spellcheck").CheckSpelling
strHidden = Range("Prod_AuditorComments").EntireColumn.Hidden
Range("Prod_AuditorComments").EntireColumn.Hidden = False
Range("Prod_AuditorComments").CheckSpelling
Range("Prod_AuditorComments").EntireColumn.Hidden = True
Range("Prod_AuditorComments").EntireColumn.Hidden = strHidden
Case "8.0"
Range("Meas_Spellcheck").CheckSpelling
strHidden = Range("Meas_AuditorComments").EntireColumn.Hidden
Range("Meas_AuditorComments").EntireColumn.Hidden = False
Range("Meas_AuditorComments").CheckSpelling
Range("Meas_AuditorComments").EntireColumn.Hidden = True
Range("Meas_AuditorComments").EntireColumn.Hidden = strHidden
Case "Summary"
Range("Sum_SpellCheck").CheckSpelling
End Select
QuickProtect
Application.EnableEvents = True
MsgBox "SpellCheck Complete", vbOKOnly, "Spelling"
End Sub
</code>

If you care to correct errors as you go, either of these may work for you?
If you care to correct errors as you go, either of these may work for you?

Sub SpellDown()
Do
ActiveCell.Offset(1, 0).Select
ActiveCell.CheckSpelling SpellLang:=1033, _
AlwaysSuggest:=True
On Error GoTo Right
Loop
Right:
ActiveCell.Offset(0, 1).Select
SpellUp
End Sub

Sub SpellUp()
Do
ActiveCell.Offset(-1, 0).Select
ActiveCell.CheckSpelling SpellLang:=1033, _
AlwaysSuggest:=True
On Error GoTo Right
Loop
Right:
ActiveCell.Offset(0, 1).Select
SpellDown
End Sub

HTH,
Ryan---

--
RyGuy


:

Thanks Ryan,However, I don't want to check the whole sheet, just the named
Thanks Ryan,

However, I don't want to check the whole sheet, just the named ranges. I
have not tried your code, but it does not appear to check the named ranges
only.
--

Tech Whiz Tom
MS Access / Excel Applications Analyst


:

I don't have time to modify the code right now, and it would be a little
I don't have time to modify the code right now, and it would be a little
difficult for me to do so because I don't have all those named ranges that
you have. Anyway, I think the macro will do what you want, once you change
it to look at the named ranges you specified. Or, just run it as is. How
much more time could it possible take to check cells that are not within the
named ranges, assuming there aren't lots and lots of spelling mistakes to
begin with.

HTH,
Ryan---


--
RyGuy


:

Thanks again ryan,Do not expect you to rebuild it ( lol ).
Thanks again ryan,

Do not expect you to rebuild it ( lol ). I will take a look at using your
code or modifying. But, is looking more and more like going to have to setup
a for next and loop through the range and copy if not hidden.... It appears
that the merged cells are causing the .specialcells(xlcelltypevisible) to not
work.

Checking the additional areas is not an option. There is not lot nor is time
the issue, but there are a lot of industrial terms, acronmyns, and
abbreviations that are going to pop the spell check box....

Thanks for trying!
--

Tech Whiz Tom
MS Access / Excel Applications Analyst


:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Book Review: C# 4.0 In a Nutshell [O'Reilly]
http://www.eggheadcafe.com/tutorial...-a2da-88dde2e6d891/book-review-c-40-in-a.aspx
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Spellcheck a single cell 11
VBA-User says active cell jumps to a different sheet upon Save. Please review my code 1
Pasted text will not spell check 5
Hide Columns Based On Date Value 1
Speeding up Hide rows code 2
VBA for Dependents shortcut menu 0
Excel loop error -- 25 rows okay, but 30 too many 1
No response to change_event 4

Top