Macro to find a word in a cell, create a transparent text zone over it, create a hyperlink

[martin brandl]

Hi, thanks in advance for your precious advice and answer !

I use MS Office 2007 Excel. Below (starting with "136.") is the typical text content of a few hundred cells in a sheet. I would need a macro for accomplishing a few tasks. I am rather new in VBA programming. I made simple macros in the past, but just by recording my different actions happening on the keyboard and the mouse.

This time, I am looking for a VBA macro that would do the following actions (from 1- to -5), after I make a single mouse click on one of my many hundred cells (to choose this cell), and waiting for me to start the macro (by hitting a combination of 2 keys, for example):

1- inside the cell chosen by me, the macro would find (or goto ?) the first word that begins with "P-", that is followed by a few numbers, and then by a black comma or by a space

2- then the macro would create a transparent text zone over this word, with or without borders (or outlines ?), that would be placed very tight over this word. (The minimum amount of characters in each of these specific words is 3, the maximum is 5. Ex: P-1, P-999)

3- then the macro would click inside this text box and create an hyperlink towards a specific PDF file in a specific folder (I could complete that hyperlink manually, once the macro initiates the procedure "create hyperlink")

4- then the macro would wait for an action from me to continue to the next word found. (exemple of an action by me: hitting the spacebar)

5- then the macro would go to the next word that begins with "P-", is followed by a comma or a space, and repeat the same actions as in -2 to -5

6- when no other qualifying words found, the macro would wait for me to click on the next cell to be "processed"

Below is an example of the text content of a typical cell (the category of the cell format is set to "standard", horizontal alignment set to "justify", vertical alignment set to "centred ", "line wrap" is selected, "merge cells" is selected, font used is Calibri 10 normal) :

136. The various items listed are best described in document P-109, P-110 and P-111, P-115, P-142, P-214 to P-224, P-228 and P-254 and piece P-2, P-33, P-34, P-36, P-37, P-38, P-39, P-40, P-41, P-42, P-43, P-44, P-49, P-51, P-53, P-54, P-113, P-116, P-117, P-118, P-119, P-120, P-121, P-124, P-126, P-129 ;

Notes:

-Each of the cells are in fact composed of a few smaller cells that I merged together, depending of the text quantity I had to place inside. This cell merging was necessary because I have many different columns containing the cells, and I wanted to define the vertical size of a cell without affecting the size of the cell in the adjacent column.

- If this could help the macro to more easily locate each word and identify it's limits, I could color each word (to be treated) in red instead of black, or I could place a red background (shade) behind each word...

Best regards !
Martin.