Rightclick macro if cell contents formatted correctly

[ahartman]

Hi all,

I'm attempting to create something that can be used on any workbook open to automatically open a file based on the data in cell. Here's the general idea:

1) User right clicks on a cell.
2) If the cell's value matches an expected format (##-A-####-## for example), a macro is available in the rightclick popup menu.
3) The user selects the macro in the popup menu if they want to run it.
4) The macro takes the cell value and converts it into a filename.
5) The specified file is opened in an external program.


I have everything done for steps 4 and 5, in the following code:

Please Login or Register  to view this content.

The rightclick stuff is a bit more difficult for me. I think I need to get into the BeforeRightClick event, but I'm not sure how to use it and I don't know how to make it so that each user need only add the macro once and then have it available for every workbook from then on.

Any suggestions would be greatly appreciated!

[solnajeff]

Hi

The following link covers most of what you are looking for and you can probably add an appropriate condition.

http://www.ozgrid.com/VBA/right-click.htm

Regards

Jeff

[ahartman]

Hi

The following link covers most of what you are looking for and you can probably add an appropriate condition.

http://www.ozgrid.com/VBA/right-click.htm

Regards

Jeff

Thanks for the link. Looking at it, I'm not sure if it will apply to any workbook, or if the user would have to add it to each book before they can use it. My thinking is that I could make something to go in personal.xls, and then each user would only have to put it in there once.

[solnajeff]

Hi

I tried out the code and it works fine, but the proviso is that it has to go in the ThisWorkbook module and I doubt that you could get around that using Personal.xls as whatever happens you would need code to link it to the Personal workbook.

Regards

Jeff

[ahartman]

Jeff,

FYI, I've been able to get this code to be available to any workbook open in excel by saving it as an XLA, and setting the temporary:= value to true in the following section in the right click code:

Please Login or Register  to view this content.

The user just needs to add-in the XLA file one time, and I think use the right click functionality once on the XLA workbook, and then it's there in their right click menu. I assume it can be removed by getting rid of the XLA file, but I've not tried that, yet.