How to remove the recent document history in Excel Ribbon using VBA

Question

How to remove the recent document history in Excel Ribbon using VBA.

I am using the code below, but it doesn't seems to work.

Sub Button1_Click()
    For i = 1 To Application.RecentFiles.Count - 1
        Application.RecentFiles(i).Delete
    Next i
End Sub

Thanks ...

Answer 1

Hi Sumanta,

There is no direct mechanism for hiding the most recently used file listing. It can be done, however, by setting the Application.RecentFiles.Maximum to zero (0).

For a detailed discussion, see Change the Ribbon in Excel 2007 by Ron de Bruin, and scroll down to the section titled "Dictator examples and Hide the MRU ('Most Recently Used') file list", with code provided by Jim Rech.

This can also be done manually. See: How to Clear and Delete Recent Documents List in Office 2007 (Word, Excel, PowerPoint).

-- Mike

Answer 2

To clear the list of recently used files, and not mess with the user's settings, the following code will work:

originalSetting = Application.RecentFiles.Maximum

Application.RecentFiles.Maximum = 0

Application.RecentFiles.Maximum = originalSetting

This will remove the recent files and then reset the maximum number of recent files back to whatever the user had initially.

If you just want to remove them individually, you can step through them in reverse order to get the job done.

 Dim i As Integer

For i = Application.RecentFiles.Count To 1 Step -1
    Application.RecentFiles.Item(i).Delete
Next

You need to run from the bottom of the collection up, because as soon as you delete one of the entries from the RecentFiles collection, all of the indexes of the remaining files change. This way, each time through the loop, you are deleting the last item in the collection.

And also, since this collection is Base 1 instead of Base 0, the last item in the collection is Application.RecentFiles.Count rather than .RecentFiles.Count-1.

I just love all those little inconsistencies in Excel.. :)