recently used files, editing

[Patricia Shannon]

This macro displays the files in the recently used file lists, and allows the
user to optionally remove selected files from the list. The files are not
deleted from the system, they just are no longer in the recently used files
list.

Public Sub EditRecentFiles()
' Created by Patricia Shannon 04/04/2006
' Displays Recently Used files
' Allows selected files to be removed from the list


Dim RecentCntr
Dim NbrRecent
Dim Response
Dim HoldRecentFile As RecentFile

NbrRecent = Application.RecentFiles.Count
MsgBox "Nbr recent files=" & NbrRecent
Set HoldRecentFile = Application.RecentFiles(1)
'MsgBox TypeName(Application.RecentFiles(1))
For RecentCntr = NbrRecent To 1 Step -1
Set HoldRecentFile = Application.RecentFiles(RecentCntr)
Response = MsgBox("cntr=" & RecentCntr & " " _
& HoldRecentFile.Name _
& vbNewLine & "Delete?", vbYesNoCancel + vbDefaultButton2)
Select Case Response
Case vbCancel
GoTo endpgm
Case vbYes
HoldRecentFile.Delete
MsgBox "File deleted from recent files list"
End Select
Next RecentCntr

endpgm:
End Sub

 

[Greg Maxey]

Patricia,

Nice. Instead of all the message boxes, why not a userform with a listbox,
a delete command button, and a cancel command button:

Code something like this:

Option Explicit
Private Sub cmdCancel_Click()
Me.Hide
End Sub
Private Sub Delete_Click()
Dim i As Long
For i = Application.RecentFiles.Count To 1 Step -1
If Me.ListBox1.Selected(i - 1) = True Then
Application.RecentFiles(i).Delete
End If
Next
Me.Hide
End Sub
Private Sub UserForm_Initialize()
Dim oRF As RecentFile
For Each oRF In Application.RecentFiles
Me.ListBox1.AddItem oRF.Name
Next
Me.ListBox1.MultiSelect = fmMultiSelectMulti
End Sub

Called something like this:

Sub CallRecentFiles()
Dim RecentFiles As UF
Set RecentFiles = New UF
RecentFiles.Show
Unload RecentFiles
Set RecentFiles = Nothing
End Sub

 

[Patricia Shannon]

Thanks. The userform would be nice, but I haven't gotten to that level of
VBA programming yet. It's something I plan to learn when I have time. I
don't have a computer at home, and have been learning VBA, mostly thru the
Help screens, on a need-to-know basis to help me with tasks at work.

 

[Greg]

Patricia,

Very impressive IMHO considering your limited means ;-). I am a VBA
novice myself and learning as I go. If I may make a couple of
suggestions:

Always declare your variables with the proper data type. For example:

Dim RecentCntr As Long

See:
http://word.mvps.org/FAQs/MacrosVBA/DeclareVariables.htm

While it doesn't hurt, you really don't need the Response variable.
Select Case can evaluate the message box response directly.

For pratice, I try to always cover all the cases and case else in the
Select case code.

Public Sub EditRecentFiles()
Dim RecentCntr As Long
Dim NbrRecent As Long
Dim HoldRecentFile As RecentFile
NbrRecent = Application.RecentFiles.Count
MsgBox "Nbr recent files=" & NbrRecent
Set HoldRecentFile = Application.RecentFiles(1)
For RecentCntr = NbrRecent To 1 Step -1
Set HoldRecentFile = Application.RecentFiles(RecentCntr)
Select Case MsgBox("cntr=" & RecentCntr & " " _
& HoldRecentFile.Name _
& vbNewLine & "Delete?", vbYesNoCancel +
vbDefaultButton2)
Case vbCancel
Exit Sub
Case vbYes
HoldRecentFile.Delete
MsgBox "File deleted from recent files list"
Case vbNo
'Do Nothing
Case Else
'Do Nothing
End Select
Next RecentCntr
End Sub


A userform is really very simple (even I stumble through the basics).
Here is a link to a webpage I created for using UserForms in place of
regular VBA message boxes. It shows the basics:

http://gregmaxey.mvps.org/Custom_MsgBox.htm

Good luck.

 

[Greg]

Patrica,

Very impressive IMHO considering your limited means ;-). I am a VBA
novice myself and learning as I go. If I may make a couple of
suggestions:

Always declare your variables with a datatype. For example:

Dim RecentCntr As Long

See: http://word.mvps.org/FAQs/MacrosVBA/DeclareVariables.htm

While it doesn't hurt, you really don't need the variable "Response."
The Select Case statement can interpret the message box directly.

You don't need the Goto statement and endpgm: line. You can use Exit
Sub instead.

I always try to handle each case and case else.

Public Sub EditRecentFiles()
Dim RecentCntr As Long
Dim NbrRecent As Long
Dim HoldRecentFile As RecentFile
NbrRecent = Application.RecentFiles.Count
MsgBox "Nbr recent files=" & NbrRecent
Set HoldRecentFile = Application.RecentFiles(1)
For RecentCntr = NbrRecent To 1 Step -1
Set HoldRecentFile = Application.RecentFiles(RecentCntr)
Select Case MsgBox("cntr=" & RecentCntr & " " _
& HoldRecentFile.Name _
& vbNewLine & "Delete?", vbYesNoCancel +
vbDefaultButton2)
Case vbCancel
Exit Sub
Case vbYes
HoldRecentFile.Delete
MsgBox "File deleted from recent files list"
Case vbNo
'Do Nothing
Case Else
'Do Nothing
End Select
Next RecentCntr
End Sub

A userform is pretty simple. Here is a link to a webpage I have that
shows how to create and use a userform in place of a standard VBA
message box:

http://gregmaxey.mvps.org/Custom_MsgBox.htm

 

[Patricia Shannon]

Thank you Greg. You're right, it's a good practice to declare the variable
data type, and to put in the "Else". Sometimes I'm just in a hurry, esp.
for a small pgm like this that's just for occasional personal use. Since I
posted it to the public, I should have done it right. I do use the Option
Explicit to force me to at least put in the Dim statements. Thanks for
showing the use of Case for the MsgBox. I'll look at your web site, and get
started with user forms.
If anybody reading this is old enough to remember, there was an early space
shuttle flight that got delayed because of a computer programming problem.
It so happened that after this occured, I took a Fortran course at night, at
the University of Alabama in Huntsville (this was before there was such a
thing as a Computer Science major). My professor worked days in the space
program at Redstone Arsenal, and told us about this problem. The problem was
caused by the fact that if you don't define a variable, Fortran creates it
for you, unless you do the equivalent of an Option Explicit.
The program had a line that was supposed to be something like:
"DO 100 I = 1, 10" , the beginning of a loop. 100 was the line label at
the end of the loop.
But the comma was punched as a period (those were the days of punched cards) .
Since the Fortran compiler ignores spaces, except within quoted strings,
this statement was the same as writing "DO100I = 1.10"
So the compiler created the variable "DO100I" and set it equal to 1.10, and
ran the code in the intended loop once, instead of the intended 10 times!

 

[Greg]

Shannon,

< I'll look at your web site, and get started with user forms.

and there you will find a Userform representation of your suggestion:

http://gregmaxey.mvps.org/Recent_Files_List_Editor.htm

Thanks for the post.