Testers Wanted-Feel good about yourself and volunteer today!

[dk]

Hello everyone and Happy New Year! Here in Oz the party is long over and we have the hangovers to prove it

Anyway, I've written an add-in for Excel that I'd like to get some feedback and testing on. I've written it using Excel 2003 but would like to get it tested on as many different versions as possible.

The add-in allows you to maintain a Favourites list in Excel, much like the Favorites in Internet Explorer. You can save favourite folders and files and quickly access them by either clicking File, Favourites or right-clicking in a worksheet and choosing the Favourites menu item. I wrote this for work as I was fed up with constantly navigating from something like G:\004Accounting\Transport\Tran_acc\XLFILES\Planning and Budgeting\Elle\Northern Region and back to H:\Development\Elle and then off somewhere else.

I know some of you may be thinking that Excel already provides this functionality with the Add to Favorites option in the Open dialog box but it seems to have disappeared in Office 2003 (haven't fully investigated). Also, I prefer to keep my favourites as websites, not clog it up with file favourites.

Anyway, you can download the file from here. Any feedback can be posted here or sent to dan@danielklann.com.

This is not a commercial project, just something that I'd like to get tested. Hopefully some of you may find it useful.

Thanks,
Dan

 

[Ivan F Moala]

I personally like it in the File Menu as is.
This is intuatively where you would find it ie File > Load.

OR

If you are use to IE, then place it on the Worksheet Menu
Then it is Only One less Click away and similar to IE....

 

[XL-Dennis]

Re: Testers Wanted-Feel good about yourself and volunteer to

Hi Dan,

I would like to have it on the worksheet menu bar and optional on the right click bar.

Kind regards,
Dennis

 

[luckycharm]

Re: Testers Wanted-Feel good about yourself and volunteer to

Running the risk of sounding to sweet - WOW
I've been playing around this thread, never taking the
time to install and test.
Silly.
Using it now for just over two hours, I cannot see how MS got away
with this not being an integral xl element.

btw, on the file menu works best to my logic.

Lucky.

 

[Ken Puls]

Re: Testers Wanted-Feel good about yourself and volunteer to

Dan,

I would leave it on the file menu. If you close all workbooks, (but leave excel open) the cell menu won't fire when you right click.

Of course, if you wanted to put it on that menu, that could help, but I still prefer to see it on the file menu anyway...

Also, I've noticed a couple of things in the rename function.

-It would be nice to have the existing file name as a default when you go to rename a file. This way, if you just wanted to, say, add a year to the description, you wouldn't have to retype the entire thing

-If you aren't intuitive enough to hit cancel when you realize that you didn't want to rename the file, and retype the same file name exactly, it won't let you use it. (I know that this is by design to avoid duplicate entries, but this wouldn't be a duplicate.) This would be a necessity if the default were put in play.

Apart from that, everything seems rock solid on Excel 2003 so far...

 

[gaynard_nelson]

Re: Testers Wanted-Feel good about yourself and volunteer to

Hi Gaynard,

You shouldn't need to reinstall Office 2000. Try the following code to get rid of the orphans. (Adapted from dk's procedures...)

Sub RemoveByCaption()
    Dim ComCtl As CommandBarControl
    For Each ComCtl In Application.CommandBars("Cell").Controls
        If ComCtl.Caption = "&Favourites" Then ComCtl.Delete
    Next ComCtl
    For Each ComCtl In Application.CommandBars("File").Controls
        If ComCtl.Caption = "&Favourites" Then ComCtl.Delete
    Next ComCtl
End Sub

If that doesn't work, you can return the true full name of the captions from the cell menu by using this:

sub CellCaptionNames
    Dim ComCtl As CommandBarControl
    For Each ComCtl In Application.CommandBars("Cell").Controls
        ActiveWorkbook.Worksheets("Sheet1").Range("A65536").End
(xlUp).Offset(1, 0) = ComCtl.Caption
    Next ComCtl
End Sub

You'd then pick out the caption name, and replace "&Favourites" with it, run the code again, and it should clear...

HTH,

Thanks very much! That worked like a charm.

quote]

CAUTION!!!

If you change the spelling per the instructions below that appear in the basCode, with the Add In loaded, you end up with the previous spelled entry in both the File menu and the and the right mouse click menu. It doesn't get removed after the change. The above code removed it for me. You need to uncheck the Add In before changing the spelling. I have tried the new version 01/06/04 and had the same thing happen to me again.

'You can change the values of these constants if you spell these words differently
Public Const CommandButtonCaption As String = "&Favorites"
Public Const OrganiseButtonCaption As String = "&Organize"
Public Const DblQt As String = """" 'Represents the " character

Probably need to add a Caution here to avoid problems.

I like the add in very much. I prefer the flexibility of having the Favorites appear both places (File Menu and right mouse click menu). I load Excel without a new worksheet, so until I load a file the right mouse click does not give me a menu with Favorites on it.

 

[dk]

Re: Testers Wanted-Feel good about yourself and volunteer to

Thanks for the replies everyone. I will post a more detailed response later but for now I'd like to address Gaynard's issue...

Thanks Gaynard for pointing this out. This issue definitely needs to be resolved. How about getting rid of the constants for 'Favourites' or 'Favorites' and just have the add-in detect if the regional settings are set to USA? If so, it will be spelt 'Favorites' and 'Organize', otherwise it will have the correct spelling ( ). Would this be acceptable or should I keep it as it is so that non-English users can change it to whatever they want in their language and find an alternative way of fixing this?

Any thoughts?

 

[XL-Dennis]

Re: Testers Wanted-Feel good about yourself and volunteer to

Dan,

Don´t overwork the language-setting!

People who find Your site and the add-in will propably know english. At least as much that they can understand the word "Favourite" :wink:

Activate an add-in with an english caption should not cause any heartattach among non-english users so just make sure it works OK on non-english plattforms and then move along


Yes, I´ve seen several solutions on this subject during the last years but there is always some drawback with every solution...

Kind regards,
Dennis

 

[XL-Dennis]

Re: Testers Wanted-Feel good about yourself and volunteer to

Dan,

In order to be available on the Worksheet Menu bar I changed the code to this:

'This section adds the button to the File menu
Should be:

    Set ComPop = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup, Before:=1)

I also prefer to have it available in the end of the commandbar (righthanded ---> mousepointer on the right side of the screen...) so You may consider to skip the "Before:=1)

As for the userform I would suggest that You add two labels on top of the list (i e above the listbox): Name and Type

Excep for above it works good with swedish version

Kind regards,
Dennis

 

[shelnrik]

Re: Testers Wanted-Feel good about yourself and volunteer to

ray: YOU ROCK!! I am using XL 2002 and your add-in works great!! I do year end books and to keep referencing the same book has been hell! With your add-in you just saved me from turning totally grey

All I can say is AWESOME

Shelnrik

 

[Ekim]

Re: Testers Wanted-Feel good about yourself and volunteer to

Dan,

Re Favourites vs. Favorites.

Another option is to place two zip files on your web site, with a short explanatory note e.g.:

Favorites.zip
US style spelling.

Favourites.zip
Non-US style spelling.


Regards,

Mike