Spelling errors-a macro to prompt user if a word is similar to a list and replace it

Hi everyone,

I have a problem when users make spelling errors in a form as it messes up my checklists (for errors!). I have supplied an example worksheet which I hope you could take a look at. Essentially I would be very grateful for a macro that does the following:

1. User enters word/phrase in column A
2. Macro compares word/phrase to list of 'Correct spelling'
3. If word/phrase is similar to one in the list then user is prompted with the following: "It looks like you have entered a [*****] expense. Correct phrasing/spelling?"
4. If user selects yes then correct with the matching word/phrase (replace contents of A? with E?).
5. If no then leave entry.

If you think you can help with this then I really would be very grateful for your reply. This is also the sort of macro that many people would find useful so that forms can be standardised etc so feel free to comment yourself in - macro fame :D

Potential solutions already considered:

While spellcheck does work to an extent, it doesn't do quite what I have in mind (standard phrasing throughout). There's is also a potential solution of using wildcards so that any word the user enters which is similar to a word on my list will be at least noted by my various formulae. Unfortunately that also doesn't give the desired result for the same reason. Any amalgamation is welcome though!

Thank you all in advance,

Limebaish

Hi Barryleajo,

Thanks for the suggestion - I've just had a look and it seems that this could be a decent workaround. While I know how to make a simple data validation list, I don't know what to change for my needs when I get to the VBA code here: http://www.contextures.com/xlDataVal10.html (just a quick google result- hope that's what you meant?). Just to clarify though, this range could have any number of words/phrases so I only really any sort of prompt if the input is similar to the 'correct spelling list'.

Thank you and by all means keep the suggestions coming!

Hi Barryleajo,

Thanks for you post and example workbook Unfortunately this isn't what I had in mind as I can't have a user adding to a dynamic range as well as selecting it from a drop down in an entry form. What I was really hoping for was an automated index+match/vlookup that gave me similar results and then checked with the user if that close match is what they wanted. If it was then the cell would be replaced with the standardised phrasing (which, to be clear is not applicable to all entries, just ones that are similar).

Thanks for your help,
Limebaish

Hi Barryleajo,

I'm really sorry if I haven't been clear enough on this, particularly as you've started writing some VBA for this. Perhaps if I clear this up you wouldn't mind having another look?

Column A will be filled with entries of whatever the user chooses. No pattern to entries. I have a checklist that has only a specific number of entries that I am worried about checking, that I've put in another list. So while a column might have the names of anything else, I'm only concerned with the cells that match what I have on my list.

However, people might misspell those entries so I would like a macro that checks the range against my list, finds similar matches and prompts the user, asking them if they meant to spell 'depreciation' properly etc, if they did then it will just replace the cell with the way I've spelled in on my list. Any non-matches should just be ignored (end sub).

Please let me know if that's clear enough for you as I really appreciate your help

Just had a further look through the code and I can see what you've done (to an extent) and it is genius Am I right in thinking that if the word matches it to a certain given percentage then it is queried? That is fantastic :D

Would you mind just doing me one more favour and making a second range so I can see how you would change the code for separate ranges (specifically named ranges so instead of A3:14 we could call it 'Mylist' and a second range to check 'Mylist2'. Thank you again, this is truly brilliant

WHAT VOODOO IS THIS? Haha wow that was incredible!

Of course I would really appreciate it if you could talk me through these steps so that I can customise it for future use but what you have just made was exactly what I was hoping for. Thank you!

So how do I add to the list of words to check for? How is it looking for misspelled words? This is going to be so useful!

Thank you very much Barry, I will of course put this one as solved!