Custom Dictionary or add words to a dictionary with VBA

You'll want to explore custom dictionaries:  http://office.microsoft.com/en-us/excel-help/create-a-custom-dictionary-HP005198530.aspx

There may be a way to make your custom dictionary "land" on a shared drive, but I haven't found documentation with respect to whether Office would manage that like a multi-user database.

You'll need to copy the CUSTOM.DIC to a shared drive somewhere, and rename it, say MYDICT.DIC

Try this - create a custom dictionary (for Excel 2007+ Options->Proofing->Custom Dictionaries and add MYDICT.DIC, making it the top priority dictionary).  Then, on the other machines, add MYDICT.DIC in the same fashion.

I just did a quick test and was able to add something to the custom dictionary, then see it not flag an error on another machine using the same dictionary.  I've not seen documentation on actually SHARING a custom dictionary so performance would really depend on whether Excel read in the dictionary each time you did a spellcheck, or whether it was read in only the first time.

It might be possible to modify the prior code question to have it refresh the dictionary based on what's there at that point in time.

Give this a shot and see if you get similar results with the version you're running.

Dave

Let's see if anyone else contributes, here.  I could not find anything (aside from 3rd party tools) on sharing custom dictionaries.  However, we could modify your original code to have Excel refresh its view on the custom dictionary at the time of messagebox load.  Not sure the ramifications of making changes - perhaps it would say the file was locked at the time of "modify" if coincident with someone else's modification.  If we get no other contributions that are easier than this, I can take a look at modifying your code to do this.

I hope there's something else however that's better than this solution, re: multi-user custom dictionary.  

Your thoughts?

Cheers,

Dave

Can I add words to the workstations dictionary BEFORE the spell check runs then REMOVE them after the check is complete?

You can probably programmatically add a DICTIONARY from a shared drive that your application would use as a supplimental dictionary, and remove it based on an event (e.g., like the file being closed).  So, if you're less concerned about muliple users needing to add to this dictionary and more concerned that they use a supplimental dictionary that's common to your app, then its probably a doable thing.

Dave

The other alternative might be to have that list of words in a hidden sheet that are added to a temporary dictionary on workbook open, and removed afterward.

The other alternative might be to have that list of words in a hidden sheet that are added to a temporary dictionary on workbook open, and removed afterward.

That's it!!  -  Now How can I do it?

If I put the "Call addtoDict"  in the Open Workbook this works but I can't put it in a sub that initializes a Userform.

Public Sub Show_Userform
     Call addtoDict
     Userform1.show
end sub

It seems that Excel is not picking up the new Dictionary until the userform closes
Any way arround this?

I'm researching now.  Can you tell me the story about why the dictionary is added "in process" as opposed to "on open?"

MAYBE a DoEvents after Call addToDict??

Dave

I tried it two ways - like you did, and as part of UserForm initialize - with the userform up, I looked at the path: C:\Users\YOURUSERNAME\AppData\Roaming\Microsoft\UProof

and MYDICT.DIC was there, and the data was populated in it.

Let me ask you - did you try spellcheck, failed, then ran the userform again and tried spellcheck?  Did you ever get spellcheck to work?

Perhaps its all getting created, but for some reason its not active.  We may need another command...

Dave

Did it ever work for you on workbook open?  I'm trying to get that to work again and am getting some mixed results.

I believe I have it sorted, but needs some cleanup.  I've got to run to some meetings so will try to handle before tomorrow.

I found code that will work that will highlight misspelled words in the userform, for correction (rather than popup the dialog).  Do you want your users to be able to add words to the dictionary?  This could be a preference.

I bring that up, because there's a flaw with this code that we have right now. The dictionary never gets used unless you go to the Excel Options and click Proofing->Custom dictionarys, or you close Excel and reopen, after having added the dictionary.

I'm still working on it but wanted to offer a solution you might find as a preference (I'm still going to dog this - what I think is a MS bug).

Dave

It works fine on workbook open.
Yes I want the Users to be able to add to the Dictionary.

I have edited the code slightly.

addToDict now deletes ALL the Dictionarys in CustomDictionarys THEN adds the Temp Dictionary.
This makes and words added go to the Temp Dictionary.

The Full story?
The VBA is a Addin that is on a network location that is Loaded on each Workstation.
When the Userform is Initialised I need a different custom dictionary depending on the Caption of the Userform.
When The userform is Unloaded I need the Std Dictionary Back (But the Addin and excel are still open even if invisible for fast access)

When I have it sorted that far I am going to store the words in a SQL database then make the Temp Dictionary on the Fly from words selected from a Select String depending on the Userform Caption.

ok - Well I always got addToDict to work as part of starting up the Userform.  Must be the add-in you just mentioned that was part of the wrinkle.

Do you have your solution, or do you want me to keep working it?  It would be useful if you would either provide your addin, or enough of the code so I can replicate what's going on and suggest rectification.

Cheers,

Dave

ok

Attached is the addin File which I have stripped as much as I can.

Go to the mSetup Mudule and Run the DicTest sub.
A Userform should open Press the New Button.
Enter some words into the Description Box and the n Press the Spell Checker Button to spell check the Description.

It does not use the words from the Temp Dict.
Any words Added are sent to the Std custom Dict.

IF when the spell check box is open you press the options button it shows the temp dict as the default and the std one off THEN it uses the temp dict and added words go to the temp dict correctly!

Any Idea's?
Solve-Web.xlam

The REAL problem as I see it is dynamically adding a dictionary and then immediately using it (without changing proofing options as you say - when you do that via spell check button, THEN it works).

This code makes the Web.DIC the active dictionary - note, I did the call to addToDict THEN executed the SHOW command via the DicTest() sub, as opposed to doing it in the Userform_Show routine.

It might perform this way if you make it part of the Userform_Initialize() routine.

I did add some value - I modified the last few lines of the addToDict() routine to make the Web.DIC the active dictionary, and on the removeDict() routine, I properly deleted the WEB.DIC file and made the previously active dictionary the active dictionary.  Also,  I commented out the "onTop" routine for the moment, so I could go to the Uproof directory to see yes Web.DIC was created properly and contains the words.

So, everything is working correctly in this version (re: dictionary gets created, is added to the collection, and made active - all before the userform starts).  However, the spellchecker does NOT use the WEB.DIC until you either close and reopen Excel OR you go to Proofing Options.  I have logged a question on E-E about this "bug".  Unless, perhaps it works for you and not me - but from your last sentence, it appears you're seeing the same behavior.

Attached = 2 files -your add-in amended - note, I start it on workbook_open, so have Excel up and running before you start.  Also, my current working file on the issue I'm talking about - making the dictionary actually work after its created - I believe Excel may need to re-open the dictionary to actually use the new one - trying to figure out how to do that or exactly what step we're missing.  

From the TIPS I found on the WEB, they don't seem to have the problem, or never tested it in the dynamic way we're doing it.

PS - recall we have a fallback we can discuss if this fails - and that's to use the tip that DOES do the spellchecking - word by word, but only highlights the word int he text box that is in error (the user has no ability to add to the dictionary/ignore) - and spellcheck button has to be hit for each word.  I think this is a last  resort as we're going to suss this out!

Dave
Solve-Web-r2.xlam
checkSpellUserForm-r2.xls

@p-plater - I think I sorted it out.

Please test both of the attached, and see if they work for you.  It turns out I was testing on a system where I have Excel 2007 and Office 2010 loaded.  I've been running this from Excel 2007, but on a hunch, I tested some code on Excel 2010 and it worked!  Both of the attached files work for me, now.  Perhaps my "mods" to your add-in code will indeed work for you!

Dave

Still can't get it to work.
How Can I send Keys to the Spell check box?
ie When the spellcheck box pops up I want to send it keystrokes to make it select the Custom Dictionary.

What I uploaded selects the custom dictionary automatically.  Its all working on my end.

When you say you still can't get it to work, I'm not sure what "it" is.  

Let's make sure we're using the same code at this point, OK?  Did you download the files I sent and test them?  

What is your environment - Office 2007 or 2010, etc?

Dave

I'm having trouble getting this to run in an Office 2007 environment, so working on that.  However, it is working perfectly in the Office 2010 environment.

What environment are you working in?

Dave

I've only tested on 2010
The downloaded files work with the Workbook_Open Event but not when I change it to the Userform Initialise.

I will need it to run on 2007.

Is there away to send keystrokes to the Spellcheck Dialog box?

Yes there is, though it can be complicated.  I'm happy to share that intel with you.  First, please humor me.  Neither of the files I uploaded to you have workbook_open to load the dictionary.  When you downloaded both, and tested both, did either work?

Please advise - as both work in my Office 2010 environment.  I need to know if they work in your Office 2010 environment.

I'm also getting help on why its not working in Office 2007.

In the meantime, I'll look at key sequencing and post a sendkeys approach.

Dave

The Solve-Web-r2.xlzm I Downloaded has a Workbook_Open Event that calls DictTest Which Loads the Dictionary then Shows my Userform.

The checkSpellUserForm-r2 is a three step process.
1. Load the Dictionary
2. Check the Spelling
3. Unload the Dictionary.

ZIf the Dictionary is Loaded with the Userform then Excel does'nt pick up the new dictionary untill the Userform is closed and re opened.

Having set them up this way :0 - solve-web loads on startup to save a few keypressess during tests.

Did either of the files work for you?  

Did the checkSpellUserForm -r2 work for you after you loaded the dictionary.

They both work in 2010 for me.  I would think they would both work or both not work in your office 2010 environment.

Please let me know then we can proceed down one plan or the other.

Dave

Yes They Both Work.

I just need the Dictionary to Load when the USERFORM in loaded and then Remove the dictionary when the userform is terminated.
(The workbook (Addin) will open when Excel opens but I don't want the Dictionary to Load untill the User opens the USERFORM)

Just need to change the code on the add-in - I thought you would do that and test, sorry about that.  I took it out of workbook_open.  After loading the add-in just run the macro dicttest() which loads the dictionary and then the userform, cleaning up on userform exit.

Please test and advise.

Dave
Solve-Web-r3.xlam

When I run that with the Open WOrkbook vcommented It does not use the Tempary dictionary words at all and any additions go to the Std dictionary

Ronald

Sorry for repetative question, but just for clarity.  Did you download the routine (which already has wkbook open commented out) and then run in the mSetup module the dicttest() routine?

Dave

Yes

OK - let's interact here quickly, to get to the bottom of this one way or the other.  When you do that, does it work?  To test, just go and edit your CUSTOM.DIC and erase the words, and delete WEB.DIC.

Running under 2010, it does or does not work?  It is working on that version for me, so want to be very clear, as this could be an installation issue.

Dave

I just had a buddy try both workbooks - my userform, and your add-in via the dicttest() routine, and it works for him just fine as well.

Are you running XP, Vista or Windows 7.  The way we have it coded it won't work in XP, otherwise its OK.


I see no changes for the add-in I uploaded (with appropriate mods), but my version is updated as there was a couple problems you didn't note, but existed (I'd hardcoded the path to my dictionary!).  So my userform version is attached, just for completeness.

By the way, my buddy said you had very nice menu icons on top of your userform.  He's deleting it, but I needed to ensure I wasn't going crazy thinking it only worked for me!

Dave
checkSpellUserForm-r3.xls

FYI -- on your other request, I wanted to advise I could not get SendKeys to progress farther than pulling up the selection option (could not hit OK then cascade back to the user).

Here's the code, anyway:

Sub MySendKeys()
    Application.sendKeys "%FIP%C{ENTER}{ESC}", True 'alt FI = Excel Options, P for proofing, alt-C for custom dictionary, then enter, escape
End Sub

When I do these keystrokes, manually, in 2007, everything works.  Not sure what the hitch is for 2007, however, I'm still trying to understand why/if 2010 is not working for you - because if I figure out 2007, we still might be at opposing ends, lol

Dave

Sorry Been on other things.

We are using XP so that must be the Problem?

Most of the Machines it will run on are 2007 and XP

Well, if its XP - that now makes a whole lot of sense.

Go to your Custom Dictionary in Excel and check the path to the files.  I think our app's paths are correct up to the last directory (but verify, to make sure).  It may be that all you need to do is change the folder ...\UProof  to ...\Proof.

let me know how it goes.

Dave

None of the Code I have been working with has any app paths hard coded.

'create path for new temporary dictionary on local machine
    sTmpDict = o_ActCustDict.Path & "\" & DictName

Ronald

Search entire project for "UProof".

Let me know.  

PS -  I don't have computer internet access till after next Sunday as I'm on a cruise with family for Thanksgiving

But can test stuff

Dave

I searched the Entire Project for "Proof" and got no results.
(All the file paths are dynamicly generated from the current custom dictionary)

Ronald

Ill test on a XP implementation when I return.

So right now you have two alternatives:

1. load the dictionary on workbook_open as originally proposed and seems that that works in all implementations we've tested.
2. Use the version proposed earlier as an alternative where miss-spelled words we're highlighted for the user in the Userform.  This version works as it doesn't depend on a dictionary to be officially setup, rather additional code is used to compare words against the temporary custom dictionary


Dave

Hey

I think I have it solved!
Tackling it a different way.
As Excel only READS the text file when it CHECKS to spelling I have simply renamed the current custom dictionary to "Old" and made the Tempary dictionary the same name as the current dictionary was.

So
I Rename the custom dictionary to "Old" on userform initalise.
Remane it back to the original name and delete the Tempary dictionary on userform terminate.

(Enjoy your Holiday)

Ronald

Strange but good!

Sounds like a winner!

If it continues to work pls post your revised solution as it will be good for the Knowledgebase.  Seems like between you and I we've wrung this pretty well.  Too bad its not consistent between Office implementations


Dave

Still working on the Solution.
You can close the question or wait abit longer till I can post the solution

You're the owner of the thread.  Only you can close, etc.

:)

Dave