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?
Zack BarresseConnect With a Mentor CEOCommented:
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
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 BarresseConnect With a Mentor CEOCommented:
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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.