excel macro to modify Hyperlink to copy and paste

Hello,

I have a workbook that has 2 worksheets. Sheet1 has a data table that is filled from an SQL database. I would like the first column of the data table to have each cell be a hyperlink. The destination of the hyperlink is Sheet2, cell A2. I would like to modify the hyperlink, so that when the user clicks on the hyperlink, the text-display of the hyperlink is copied into sheet2 cell A2. Initially, I thought this was simple. I added the event code shown below:

Please Login or Register  to view this content.

this worked, however there was a problem. I manually added the hyperlink to all cells in column1 of sheet1. However, this made the TextToDisplay property the value of the first cell for all hyperlinks. So I am curious if you have a solution to add hyperlinks to all cells in the first column, with TextToDisplay property set for each individual cell. I have a few ideas of how to do this, but I am curious what is the best way to do this with the fact that the number of rows in column1 will vary as the size of the SQL query changes?

ok, thanks