LebGeeks

Stygmata

Banned

autoreplacement in Excel 2007

hello
in my job , we work on excel a lot .
we have a report that have several abbreviations , per example : exp for expenses , etc .
my need is that if i write * per example it will change it into exp .. if i write # it will change it into something else .
is this possible ? maybe with autocorrect ?
my main goal is to eliminate the human errors of writing esp in place of exp or something similar
thank you

eliminator

Member

Re: autoreplacement in Excel 2007

Hello : )

Solution1: use autohotkey scripting, this needs to be installed at each machine, scripts can be the same on each user machine. For example you can write in the scripts, if it detects "exp" --> automatically insert "expenses". But this will happens at the level of all softwares and not only excel. This is not quite an easy solution

Solution2: write a VBA script in the excel that does whatever you want, and then you simply run a macro.

Solution3: Use data validation inside excel, from excel's help "You use data validation to control the type of data or the values that users enter into a cell. " this way you limit the users input.

Note that in all solutions, you need to share either the script, or the excel sheet prepared with data validation, because you need to have this behavior propagated to all the users.

Hope it helps a bit

xazbrat

Member

Re: autoreplacement in Excel 2007

Another way to limit what the user can enter by using dropboxes.  You give the user the choice of expense, revenue or whatever other category you choose---you limit errors by the users and if you design a vba macro properly, you can have it treat the values appropriately.  Now this doesn't eliminate use error, but it greatly reduces this one.

As noted above, you will need to use the embedded macros on all the workstations for this to work properly.