Spell check - VBA vs. Manual

[dugong]

Hi all

I have initiated a spell-check in VBA using:

Please Login or Register  to view this content.

However, when going through the actual spell-check via the macro - it does not highlight the cell it is checking for the spelling.

I originally had macro go to cell A1 before spell-check but it did the same hence trying to select a range - thought that would solve but didn't!

If I do it manually (i.e.: outside of the macro), it does highlight the cell it is checking so you can see what you are actually spell-checking.

What can I do to make it show the cell it is checking via VBA..?
(I looked at SpellingOptions but it is not an option in the list)

Any help appreciated, dugong

[Andy Pope]

Please Login or Register  to view this content.

[dugong]

Hi Andy Pope

Thank you - works fab

dugong

[royUK]

Why do you want it to highlight cells, that would slow the process down? This code is a bit neater

Please Login or Register  to view this content.

Or just

Please Login or Register  to view this content.

[Andy Pope]

Hi Roy,

If you use that code the incorrectly spelt word is not hilited.
It is in the dialog but unless you hunt for it in the sheet it may not be obvious or easy to locate.

If you do not specify the range range by selection the spell checking will be for the whole sheet.

[royUK]

When I ran the edited code it picked up spelling errors & displayed the dialog to amend the incorrect word(s). I just used a list of words not sentences.

[Andy Pope]

yes but could you tell which cell the dialog was refering to?

[royUK]

yes but could you tell which cell the dialog was refering to?

no, but I couldn't see any reason to, unless it was part of a string or sentence

[dugong]

no, but I couldn't see any reason to, unless it was part of a string or sentence

Hi RoyUK - I need to see the contents of cell as I need to work out the context the sentence is written in.

AnyPope/RoyUK (or anyone!)
I used the code suggested:

Please Login or Register  to view this content.

However I have hit a snag - when I run it on a larger file (not my small test size), it wants to perform the spell-check again - and starts again.

I just ran though ignoring most and changing one or two just to test - could it be trying to re-do the 'ignored' ones?
- Is there another part of the code I need to add in..?

Thanks again - dugong

[dugong]

Is there another part of the code I need to add in..?

anyone...?

Thanks again - dugong

[Andy Pope]

You need to explain further.
What exactly causes the spell checker to appear again? The code launches it once. Do you have other code that would cause the code to run again?

[dugong]

The code launches it once. Do you have other code that would cause the code to run again?

Hi AndyPope

Nothing as far as I know/can see!

It appears to be re-checking the items that I have actually ignored when doing the initial spell-check - almost as if it will only accept it when it changes..?!

If you can't think of a reason why it would do this - or a way around it, I can try to post an example on later today..?

dugong

[Andy Pope]

Post example, detailing actions required to reproduce the problem.

[dugong]

Post example, detailing actions required to reproduce the problem.

Example posted

What it should do is....
* Take a sheet of verbatims/text, trim and make it proper.
* When 'propering', it also replaces certain quirky items that proper changes (ie: doesn'T to doesn't).
* Then pulls columns it needs onto another sheet and also;
* Does a length check on the verbatim/text length
* Spellcheck is called in and after spell-check it should refresh the verbatim/text length sheet as the length may have changed.

Maybe the error is where I have dim-ed the variables.
I had a bother with another item and a solution found was putting them as global. I did that but it appeared to make no difference (famous last words!).

I am a bit of a learner (actually that is an understatement!!!! ) so am unsure of passing values to a second sub-procedure so maybe thats wherein the error lies..????

If you can work out why it is re-checking all the ignored items would be great!

Any help is greatly appreciated

dugong

[Andy Pope]

It runs twice because you tell it to

Please Login or Register  to view this content.

replace with

Please Login or Register  to view this content.

[dugong]

It runs twice because you tell it to

Doh!!!! I guess thats what happens when you don't fully understand the code suggested!

I also think my thoughts are very basic with VBA (as I am trying to teach myself) and so follow (what I think..) are logical steps and then find it confusing when there are different ways to do the same thing!

Thanks Andy for taking time out to look at it (and for your suggestions!)! It really is appreciated!

dugong

[dugong]

Please Login or Register  to view this content.

Does the "ID:=2" tell it to spell check? I assume it does?
If that is the case - how can I find out what other things VBA can do using this statement..?
I tried searching on each item (CommandBars, FindControl, ID and Execute) but that appeared to be of no help!

I dont think this is another question as it is part of the same thread - if it is considered so, let me know and i will repost in seprate thread!

dugong

[Andy Pope]

All buttons in excel have a unique ID value.

So yes in theory you can use other values.
You can use the Commandbar collection to loop through and discover ID value and caption of control.

[dugong]

Thanks Andy

[MSFSC]

This is great! I would never have guessed spell check is such a problem! It took about 3 hours of searching, reading, and trying, but I finally came across this very simple solution (way too simple for me to think of by myself!)

Rob