Add-In question

[ahartman]

I have written a module that opens a file based on the contents of the active cell, and saved that module in personal.xls. I've also written some code that adds an item to the shortcut (rightclick) menu which runs the file opening code (the main subroutine is GetDrawing) when clicked, and saved this rightclick code on personal.xls sheet1. I want this rightclick functionality to be available in any sheet I open in excel. The right click code is below:

Please Login or Register  to view this content.

I want to distribute the file opening module and the right click functionality to my colleagues. Everything I've read here and via google tells me that add-ins are the appropriate tool for this situation. I've saved personal.xls as an .xla file, but I'm uncertain of what I need to do after that. I've read 20 or more threads here and still feel no closer to understanding what I need to do. I know that I could simply copy personal.xls and distribute that with instructions on where to save the file. However, I don't want to force people to overwrite their individual personal.xls files, which they may have customized for their own purposes.

I think my problem lies in the fact that the rightclick code is in sheet1 of personal.xls. If I've deleted the commandbar control for the new shortcut menu item, I can add it back again by unhiding personal.xls and right clicking on sheet1. This brings back my menu item and also makes it available on any other sheets in any other workbooks. I've also got the same code on sheet1 of the add-in, but since I can never actually see the sheet, I can't rightclick it to activate it's version of the rightclick code.

I feel like there's some fundamental architecture/design concept I'm not grasping here, and that I'm dancing all around the solution without being able to see it. I notice that there's also a SheetBeforeRightClick event that the help file says will work for any sheet, but I've tried substituting this into my code with no success.

Do I need to use the BeforeRightClick event, or can I simply add a CommandBars("cell").Control in the file opening module? Is there a way to make an add-in that I can distribute to others which will let them have a new rightclick menu item that opens a file based on the active cell's value? Any suggestions, advice, sample code, or directions to another source of information would be greatly appreciated!

[ahartman]

Again, sorry to answer my own question, but I think that I was unnecessarily using the BeforeRightClick event and that was making things overly difficult. Here's the code for the right click stuff that I got working:

Please Login or Register  to view this content.

I saved it in ThisWorkbook of my .xla file, and it works like a charm. Thanks to the forum for the space I needed to discuss my problem and workout my own solution!