Using Autocorrect for better productivity

I have been using Excel’s native Autocorrect feature for better productivity for a long time which is code for helping me remember long or complex formula shortcuts mainly and other stuff. Recently I decided to up my game on using this really great Excel feature more effectively. This is what I did.

1) Firstly, I wondered could there be a command I could add to my Quick Access Toolbar (QAT) to access the Autocorrect dialog more easily? Its a bit laborious to go Excel Options \ Proofing \ AutoCorrect Options

Yep there is a command there under Commands Not in the Ribbon called AutoCorrect Options so that’s good but my bad for not noticing this years ago! Anyway so by adding this to my QAT, it makes accessing this functionality all the more practical. For more detail on how to do this just google how to Customize the Quick Access Toolbar.

2) Second thing I did was put all my custom entries together so they were easier to look up because I can’t remember my own abbreviations months later. You don’t want to have to arrow through them all in the AutoCorrect dialog which is one of those cruel Excel dialogs that doesn’t expand and shows only 5 rows of hundreds of rows at a time. So I added a prefix of “x” before each of my an Upper Case custom shortcut abbreviations. So now a simple “x” entered in the AutoCorrect Replace: field in the dialog takes me to the start of my custom entries and I can just arrow down to see the rest. So this is looking much better to use now.

Even if I had left it there, this was a huge improvement in how I was using Autocorrect in the past.

3) So then I wanted some VBA to show just my custom entries somehow so I could review and hopefully copy and paste them easily. Guess having this macro is not totally necessary, but a nice to have and was a fun challenge.

The idea is because I now have my custom Autocorrect entries all starting with “x”, it should be fairly easy to just filter for these and load them to a User Form text box (you would think).

But it wasn’t so easy, but after a fair bit of searching, I found a great starting point at Debra Dalgleish’s fantastic Contextures.com site (Create & Copy AutoCorrect List) which is actually some code from an old Newsgroup post by Dana DeLouis. This code dumps all the Autocorrect contents to a spreadsheet. That’s pretty handy.

But I wanted to return just the items starting with my “x” prefix and I want it to go to a Userform text box. But if you are just happy dumping to a spreadsheet and go no further, this link already gives you a good solution. Note I found problems with this code as some of my Autocorrect custom entries for formulas had the “=” in them which was not such a smart idea as VBA can’t add invalid formulas to a worksheet which I created. So just drop the “=” and the code in this Contextures link works great.

So my solution involves…

1) Setting up a simple User Form (UF) with just a Text Box and a close command button. I wanted the Text Box control from the VBA design mode Toolbox so I could select text to copy and paste. The initialization code is just simple as follows:

				
					Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Me.Caption = "My Fx Autocorrect Shortcuts"
End Sub

2) then I wrote a VBA Function to return the Autocorrect contents filtered for “X” entries only in a string with a new line per entry

3) then finally I wrote a VBA Sub to load this to the UF and show it.

both below…

				
					Sub OutputCustomAutoCorrect() 
' theExcelFactor.com 
' return Autocorrect custom entries filtered by "x"
'   from Function OutputCustomAC to User Form "ufAutocorrects" and text
'   box FxAutoCorrects

Dim frm As ufAutocorrects

    Set frm = New ufAutocorrects
    With frm
    .FxAutoCorrects.Text = OutputCustomAC
    .Show
    End With

    Set frm = Nothing

End Sub

Public Function OutputCustomAC() As String
'theExcelFactor.com 
'Output Autocorrect array results filtered to "x"

    Dim myList As Variant
    Dim i As Integer
    Dim c As Range
    Dim FilterFor As String
    Dim MyReport As String
    
    myList = Application.AutoCorrect.ReplacementList
    MyReport = ""
    FilterFor = "X"
    
    For i = LBound(myList) To UBound(myList)
                        
            If UCase(Left(myList(i, 1), 1)) = FilterFor Then
            MyReport = MyReport & myList(i, 1) & ":  " & myList(i, 2) & vbNewLine
            End If
    Next i
    
    OutputCustomAC = MyReport

End Function

Voila!

So this shows the Replace and With i.e. the detail separated by a “:”

I access this from my custom Ribbon command that is linked to my macro like this.

You can add custom format codes or anything you need to remember like ABN numbers, addresses, whatever.