This is an Add-in that I use to access some frequently used files. I’m a keyboard guy, thus I eschew the mouse whenever possible. The Most Recently Used file list is handy, but my once-a-week or once-a-month files are never on there, so I needed something different.
It’s nothing fancy, mind you. You can add or delete the current workbook from the list, and that’s pretty much it. Also, because of the keyboard thing, it only holds 9 entries. (If I have to use my mouse, I might as well just open it normally.) I purposely made it simple. There are some features that might be nice, like sorting or nested folders, but I don’t need another bloated add-in.
Note that I’ve run it through some mild tests, but haven’t really tried to break it. It should work in 97 and above. I’ve also tried to make it internationally friendly, but I’ll let you be the judge of that. If you would like to break it (or just use it and find that it breaks) send me your feedback.
Download Favorites.zip
Stick the file in your Add-ins folder and load it using Tools>Add-ins.
Great idea. I loaded up the add in and gave it a try. Two thoughts:
I would rather have a first level menu option (ie: add a “Favorites” directly to the CommandBar1.
How do I clear the 9 items that are already on the menu?
Opps! Forgot to mention . . . when I tried to “Add Current” to the menu, I get a message saying Favorites is full. I see that I need to click on all 9 of the current entries to delete them and then start adding my own.
Ok, one last comment. I deleted all of the existing entries, added one of my own. When I click on Favorites, all I have is “Add Current” and “Delete Current”, but not item I just added. If I try adding it again, I get the dialog that says “File already exists on Favorites as item #1?. Deleting current and trying to add again provides no joy.
I once added some code to my Personal.xls to create a similar device. I called it the “Work” menu and had it directly after the “Help” menu on CommandBar1. Can’t remember why I stopped using it. I think it was because I created a folder on top of the “start” button menu and added shortcuts there to all the files I used regularly. Keyboard shortcuts were very simple because all I had to do was hit the “Windows” key, the first letter of the folder name and then either a number or letter for the file I wanted to open.
I have a suggestion –
To add more ‘Favourites’ in the menu, you can go into the alphabets after the numbers. So, you can go from 1 to 9 (why not 0 to 9?) and then go A, B, C…
Nice, I use a Favourites add-in from which is something similar but it has an “organise” function. That one seems to have a bug or two (open folders doesn’t seem to let you open files for example) but as the code is available to tinker with that could be fixed.
(Apologies if the link gets messed up, first time trying to put one in this blog).
And sure enough, it got messed up. Link should have been to http://www.danielklann.com.
Rick: I meant to delete all those entries before uploading, whoops. I can’t imagine why it’s not showing up on the menu. I’ll look at it again.
abhijit: Letters instead of numbers? Genius.
A few more anomalies: My Favorites menu appears as the 11th item on the File menu, just below “Web Page Preview” and just above “Page Setup…” instead of just above the MRU list. (XL03-SP1) Also, I had unloaded the addin last night and then reloaded it. I deleted a few of the existing entries and added my own. When I started up XL this morning and checked the Favorites menu, all of my entries were gone and the 9 existing ones were back. I repeated the process with the same result.
Great feature, thank you very much for developing it.
It seems though, reading the other comments, that no one else had the problem of the favorites menu being restored to its original state when closing Excel and opening it again.
To solve this problem (I really hope I’m not actually the only one that had it) I’ve simply added the following line to the end of the AddCurrent & DeleteCurrent subs, just before the End Sub statement:
Workbooks(“Favorites.xla”).Save
Seasons Greetings (to all involved),
Peter
Two comments to an ingenious xla.
1. When there is only one favorite left (found by deleting the default set), that single favorite does not list. When all defaults are removed, and the new ones installed, the first does not list, but after two, ALL are listed. Related to #3 above.
2. Peter’s suggestion gave me an alert as an undefined variable. I don’t see what’s undefined, but I changed it to
ThisWorkbook.Save
and got good results.
Thank you for a fine tool.
Michael
Dick, I’ve amended your add-in to include 15 files instead of 9, but it fails to open any files above 9 because of this line:
set sCap = HandleAmp(Right(.Caption, Len(.Caption) – 3), False)
If I change to:
set sCap = HandleAmp(Right(.Caption, Len(.Caption) – 4), False)
10 and above open. I can’t seem to get an OR statement to work. TIA
Greg
Greg
lPrefix = Instr(1,.Caption,”.”)+1
…HandleAmp(Right(.Caption, Len(.Caption)-lPrefix),False)
That’s air code, but the theory is sound. Look for the period in the caption and add one for the space.
Dick –
How should the code be modifed to run an auto_open macro which may or may not exist upon selecting a favorite?
…best, Michael
Nice tool!
I was just about to say something similar as #13 ^^
auto_open macro causes an error “Application-defined or object-defined error”
Answering my own question, I think. In the mod0nAction module, in the OpenFavorite() subroutine, after the line:
Set wb = Workbooks.Open(sCap)
Add the two lines:
On Error Resume Next
Application.Run wb.Name & “!Auto_Open”
This does not always work, but you are no worse off than before. Apparently, if the VBA code in wb.name is not properly compliled, in EVERY subroutine therein, the “Auto_open” macro is “not found” even though you know it’s present. I have not found a consistent fix to eliminating the compiler complaints, but you can test by commenting out “On Error Resume Next”.
…Michael
very convenient. everytime I need not go back to the desktop to click the shortcuts to files.
instead of giving the complete path will it not be more convenient to only have the filename so that it will be easier to select.
I have been looking for a tool like this one, thanks.
When I click to delete each of the items that you have in the list, this message books pops up.
“Workbook not found. Click Yes to find the workbook yourself, No to remove the item yourself.”
I’ve tried clicking Yes and No but no change. I’ve tried adding or deleting and but it doesn’t seem to work for me. Can you please give me some suggestions on what to try?
Thanks,
Gritz
Hi Dick
Thanks for making this available – very useful.
However, I have a problem. When I open one of my most favourite spreadsheets, your favorites entry on the file menu disappears and so I cannot use it to ‘add current’. As soon as I close theis particular sheet, the favorites entry is back.
Any ideas why this happens please – your add-in works fine for all my other favourites. Could I manually add the extra sheet perhaps? As you can tell, I’m not a VBA expert – had a look in the coding but couldn’t see where to add it, if so.
Regards,
Vivian
Poole, UK
Oh no! Worse still, all the original (Dick’s) entries have now reappeared…
I see others have had this problem but assumed the code had been updated to overcome this problem. Is this possible please…?
Vivian
Sorry, email problems on the address I gave you earlier. Please use the above if you want to contact me.
Regards,
Vivian
In the end, I’m afraid I found this add-in very unreliable and have stopped trying to get it to work.
However, a much better version of almost the same thing can be found at http://www.danielklann.com/excel/excel_favourites_add-in.htm
Works very well – reliable and not limited to 10 entries.
Sorry Dick!
Vivian
Vivian: After your first comment, I decided to finally fix the add-in. It didn’t take long before that moved down the priority list. I’m glad you found a suitable alternative, and I hope everyone reads these comments before downloading. Someday, I’ll fix it.