Use VBA to auto Hyperlink files based on input

Hi all,
I want to create a VBA macro (or sub or whatever it is called) that will automatically place a hyperlink in a chosen column based on data pulled from other cells in the row. My docs are now found on a server. Example: 5159.pdf. I need to be able to auto hyperlink at cell "B9" and so on after i select the drop list input at cell "B8" and key-in input in cell "A9" (Field Name - Doc. No.).

Every time i key-in input at Doc. No., my hyperlink column start from "B9" onward would create the auto hyperlink

Example hyperlink: \\servername\folder\<B8><doc.no>.pdf and text to display should be "Yes"
Path \\server\folder\ should be hard coded or any.

hyperlink.gif


Really appreciated your help... Tq.

Tq OllyXLS,

the code given to create the auto hyperlink and text to display is work. Tq so much.
However the file extension doesn't appear after the doc. number ("A8") Example : \\fserver\folder\filelocation\5152.pdf
2nd, if i delete the doc. number, the link still exist.
3rd, if i drag or add new row to add more data, error popup as follows:-

Run-time error '13'
Type mismatch

VBA Error - If Not Intersect(Target, Range("A8:A9999")) Is Nothing And Target.Value <> "" Then


Appreciate your response ....

Your kindness is highly appreciated.

how to mark it .... i'm new here ...

Dear Olly,

How can i recall the hyperlink based on sheetName.
I got another sheet where the cell contains sheet name, path (file location) on Sheet Name call "LIST".
The hyperlink auto created based on sheet name given.
Example : "Sheet1" rename to "INC" ....

Really appreciate your assistance ....

Originally Posted by Olly

Good, glad it does what you want

You can mark this thread 'solved' now.