VBA Code for Autocorrect type routine

I have spreadsheets that I would like to type in one thing and it enters another such as autocorrect.  I only want this to happen with specific spreadsheets; so, do not want to make permanent changes to autocorrect.

Is there VBA code that I can use to do this or use VBA code to modify autocorrect for specific spreadsheets?

The following is code that I created to modify autocorrect; however, it will not add the replacements when I open the spreadsheet nor delete them when closing the spreadsheet.  

Private Sub Workbook_Open()
    Application.autocorrect.AddReplacement what:=".", Replacement:=":"
    Application.autocorrect.AddReplacement what:="01", Replacement:="Chicago"
    Application.autocorrect.AddReplacement what:="02", Replacement:="LA"
    Application.autocorrect.AddReplacement what:="03", Replacement:="Denver"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.autocorrect.DeleteReplacement what:=".", Replacement:=":"
    Application.autocorrect.DeleteReplacement what:="01", Replacement:="Chicago"
    Application.autocorrect.DeleteReplacement what:="02", Replacement:="LA"
    Application.autocorrect.DeleteReplacement what:="03", Replacement:="Denver"
End Sub

Is modifying the autocorrect the correct approach or is there a better alternative?  If so, how can I make the code work correctly?

Thank you in advance for your help.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Zack BarresseCEOCommented:
Hi there,

The Workbook_Open routine is good, but there is no Replacement property of the DeleteReplacement method. Just remove that part of the code and it'll be fine.

Application.AutoCorrect.DeleteReplacement What:="."

Open in new window


Zack Barresse

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jack3_99Author Commented:

Thanks for your comments.    

I have only been able to add to the autocorrect if I manually run the routine--it does not work by only opening the workbook (Workbook_Open).

I made the change to the Workbook_BeforeClose routine; however, it still does not remove the changes.

Attached is a sample of the workbook.

Thanks again for your help.
Zack BarresseCEOCommented:
Ah ha, I see the problem. You have the code in the Sheet1 module, when it should go in the ThisWorkbook module. Move the code and you'll be off to the race. :)

jack3_99Author Commented:
I appreciate all of your help.  Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.