Although the downloadable file is in Excel 2003 format, I never needed one of these until Excel 2010. Now I use the recent files list a lot more, and I want to be able to tidy it up without having to right-click files one at a time. Hence the creation of this simple tool, which allows you to delete multiple entries from the list.
The form’s initialization code fills the listbox with the recent files. It sets the listbox’s style to the fabulously clunky fmListStyleOption, and MultiSelect to Extended. This means you can select multiple files using the control and shift keys. You can’t uncheck an item though, except by selecting another.
For i = 1 To Application.RecentFiles.Count
Me.lstRecentItems.AddItem Application.RecentFiles(i).Path
Next i
.ListStyle = fmListStyleOption
'you can use ctrl and shft to select multiple files
.MultiSelect = fmMultiSelectExtended
.ListIndex = -1
End With
The UserForm also has code from Andy Pope for making the form resizable, which I tinkered with a bit.
The Delete button code loops backwards through the listbox, deleting the corresponding file if the item is selected. It goes backwards for the same reason you delete rows from bottom to top – otherwise the indexing gets messed up and you delete the wrong files.
Dim i As Long
With Me.lstRecentItems
'If nothing's chosen
If .ListIndex = -1 Then
GoTo exit_point
End If
For i = .ListCount - 1 To 0 Step -1
If .Selected(i) Then
'List is zero-based, RecentFiles is a one-based collection
Application.RecentFiles(i + 1).Delete
End If
Next i
End With
'If you're looking at the Home screen this will update it
Application.ScreenUpdating = True
exit_point:
CloseForm
End Sub
I’d like it if you could bring the “pinned” items to the top of the listbox, but I don’t see any properties or objects to control that. Recentfiles seems to be simply indexed with the most recent first.
If you play around with this and, like me, delete all the files from your list, you can fill it back up with fictitious ones.
Dim i As Long
For i = 1 To 20
Application.RecentFiles.Add ("c:/test" & i)
Next i
Application.ScreenUpdating = True
End Sub
Download the Recent File Deleter zip file.
I use it for quite some time and I wonder if you can add other features.
In a section of the way to have a listbox that lists the books ai open and to close, save and delete files open
fazz, I’m glad it’s useful for you. Sorry, I probably don’t think I’ll be able to do that. If you ever get it done, be sure to let us know.
Buena Tarde, me pueden ayudar para que en vez de borrar los “Recientes” borre información de una tabla Llamada “país” de la cual se cargan los item para el listbox? Gracias
Gracias por el aporte, soy nuevo en VBA, hace mucho busco por la red un ejemplo de Listbox Multiselect como éste, claro que debo adaptarlo a mi necesidad. Lo que necesito es que en vez de borrar las listas recientes borre datos de una “Tabla de Excel” de donde mismo toma los datos para la lista. Como ya dije soy nuevo. Bendiciones comenten si me pueden ayudar, hasta pronto
Hi Luis. Sorry, I won’t be able to help with that. You should be able to find examples on the web though, and get help on a forum like Stack Overflow.