Using vba to hyperlink cells when populated

[carlandtina02]

Hi All,

Is there a way of using VBA to create a hyperlink to a cell only when the cell is populated, for eg. All cells in colum E will have a reference sheet number (12345678) and when selected (clicked on) will look in C:\Documents and Settings\carl.walker\My Documents\Machine DWG for that file named 12345678 and open it?

The cell currently gets populated by a userform (if that makes any odds).
I have uploaded a copy of the workbook to veiw.


Any help or guidance would be much appreciated.

Thanks in advance
Carl

[mrice]

Try adding the following to the Sheet1 tab

Please Login or Register  to view this content.

I've assumed that the file has a .txt extension.

[carlandtina02]

Hi Martin,

Thanks for your work.

When clicking on the hyperlink the msg that appears is "Cannot open the specified file". You mentioned does the file have a txt extention? Im not clear what this is but the file that I am trying to open is pdf if that helps.
The text appears as if it is a hyperlink and when you hover over it, it displays the correct directory too.

Kind Regards
Carl

[mrice]

OK - can you try

Please Login or Register  to view this content.

[carlandtina02]

Hi Martin,


I have put the code in sheet1 but nothing happens, none of the cells appear to have hyperlinks unlike the first code.
I have uploaded the workbook just in case it's me!


Thanks for your help
Carl

[mrice]

It's probably that the string that I am using to generate the path and filename is not exactly the same as on your system.

Please Login or Register  to view this content.

If your replace Cell.Value with the number - does this give the correct path?

[carlandtina02]

Hi Martin,

I removed all hyperlinks from column E then in the first code you done for me I changed "txt" to "pdf" , then I cut all content from that column and pasted it back in the same column.

After this that lovely blue line appeared under the Ref number indicating a hyperlink, better still when I clicked on it...it worked

Thanks for taking the time to help Martin, it's much appreciated!

Regards
Carl