• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

automatic spell check

I

iceblocks

Member
Hi all,
I need a macro to do automatic spell check on a few cells when user enter comments in them. I prefer not to have users clicking on a button to start the spell check process.

I have the following codes which run fine if I click run every time but is there a way to make it automatic?

Code: 
 Sub SpellCheck()

 Dim Myrange As Range
  
  
  For Each Myrange In Union(Range("L10"), Range("L14"), Range("L29"))
  If Application.CheckSpelling(word:=Myrange.Value) = False Then
  Myrange.Font.Color = vbRed
  Else: Myrange.Font.Color = vbBlack
  End If
  Next
End Sub

Is the 'application.intersect' need in this case?

Many thanks in advance.
 
Hi iceblocks

Put the following in the worksheet module you wish to run the code from.

Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Myrange As Range
If Not Intersect(Target, Range("L10,L14,L29")) Is Nothing Then
    For Each Myrange In Union([L10], [L14], [L29])
      If Application.CheckSpelling(Myrange) = False Then
        Myrange.Font.Color = vbRed
      Else: Myrange.Font.Color = vbBlack
      End If
    Next
  End If
End Sub

Take care

Smallman
 
Hi Smallman,

I know its late in Australia but I was curious to know how will this work on continuous range. Example range A1:D10

Regards,
AM:)
 
Awesome Smallman, it is working. Thank you so much.

As an extension to this, just wondering is there a way to change the font colour of all spelling error(s) to red only and not every word in the cell?

The reason why I ask is that some of our clients' name are coming up as 'spelling error' but in fact it is not a typo. If spelling error(s) found or deem by Excel are changed to red, then it would be easier for user to sort out which error is actually a typo instead of going through every word in the cell to figure it out. So effectively if all I see in red are our client name, then I will know it is not a typo.

Many thanks in advance.
Iceblocks
 
Hi ashish

It was late last night in Oz. I was either watching tennis or sleeping when your message came through.

It is relatively straight forward to adapt the code. Just change these two lines in the above coding construct.

Code: 
If Not Intersect(Target, [A1:D10]) Is Nothing Then
    For Each Myrange In [A1:D10]

Should work well.

Take care

Smallman
 
Hi Smallman, any thoughts on only making the spelling errors in red and not everything in red?
Many thanks.
Iceblocks
 
Hi Iceblocks

I am not sure what you mean. In all my tests words spelt correctly are black. I am well pleased with the result.

Take care

Smallman
 
Yes Smallman, when all words in the selected range have been spelt correctly then every word is in black. However, when there is one typo then every word in is changed to red.

For example, one of the client names is 'Timy', but Excel think this is a typo as it is not spelt in the standard way of 'Timmy'. In this situation every word was changed to red because Excel think there is a typo. If you type in ‘Smallman’ then every word is changed to red as well, when ‘Smallman’ is not a typo. Is there a way to just have the typo in red, like only Timy, Smallman in red. That way, user will know quickly that the typo identified by Excel is not really a typo and then they don't have to read through the whole cell again to find a typo when there are no typos in the first place.

Hope I have explained the situation more clearly this time.
Thanking you in advance.
Iceblocks
 
Hi

This bit:

every word is in black. However, when there is one typo then every word in is changed to red.
Click to expand...

These words are not correct. Have a look at the attached unaltered file.

Change words form wrong to right. Works perfectly doesn't it?

So I am unclear about what you are saying in your post. I think you got it right post 6 and lost your way a bit after this point.

I am really pleased with the way this worked out.

Take care

Smallman
 

Attachments

  • Book1.xlsm
    15 KB · Views: 2
Thanks for looking into this Smallman.

Not sure which macro I should be looking into at your file though.

Anyway, I have attached a file which shows that when I type in the word 'Timy' then all words in the cell are changed to red.

cheers,
iceblocks
 

Attachments

  • Spellcheck.xlsm
    15.1 KB · Views: 2
OK Iceblocks

Your problem is one of articulation. You mean to say you want one word in an entire phrase to be highlighted Red not the whole phrase.

This is a different problem to the one outlined in your thread to this point. You are not dealing in single words you are dealing in phrases and you want incorrect words in the cell to be coloured red while the words spelt correctly in the same cell are left black.

This will require a different approach.

Take care

Smallman
 
Thank you so much Narayan for finding the new codes, it is exactly what we are after.
And thank you Smallman for helping me with my original codes.
Apologies for my poor English. I am still learning this language and the VBA language.
 
You must log in or register to reply here.
Back
Top