VBA to automatically Hyperlink cell text with similar file name

hi,
i currently have an excel sheet where i am entering the issued certificates for the jobs done.
Some examples for the certificate names are
JAN18/161/C1-C10
FEB18/185/C1
FEB18/190/C1 & FEB18/190/MPI/C1

I have similar names for the files that i save. But the problem is that as windows does not accept "/" in the file name i have replaced "/" with "." in the file names and they look like
JAN18.161.C1-C10.pdf
FEB18.185.C1.pdf
FEB18.190.C1 & FEB18.190.MPI.C1.pdf

It would be very difficult for me to link them manually, as there are alot of jobs. And there are not just certificates that i have to deal with. i have invoice, quotation, delivery notes with the same requirement.

Now i want to be able to click the cell with certificate name text and i want the file to open.
And i want the hyperlinks to update automatically as i put the certificate name. if there is no file with that name it should stay as plain text.
It would be easier if the color for the certificate name text can change when it gets hyperlinked so that it becomes easier to identify that it has been linked.

can someone please help me with this?

Hi Ahmed,

Just try to replace "." with "/" in your code

you can use the Replace() function. [a = Replace("JAN18.161.C1-C10", ".", "/")]

You can set the file name to string and try replacing


Hope this works,

Best Regards,
Hussain

Originally Posted by Mumps1

I am assuming that your data is in column A of Sheet1. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. In the code, change the path (C:\Test\) to the folder containing your files to suit your needs. Close the code window to return to your sheet. Click on a cell in column A.

Please Login or Register  to view this content.

The code is perfect. but there are a few issues i am encountering while using this.
A single mouse click or even using the arrow keys to move to the cell is opening the file. if i want to edit a cell, at first the hyperlinked file opens and then i will be able to edit the file. this is happening with empty cells aswell. so if the hyperlinked cells could be opened via another method it would be easy. something like "ctrl+left mouse click".
If the cell is empty and i try to add text to it i get an error at first and then i have to exit it and enter again. "run-time error '-2147221014 (800401ea)': cannot open the specified file"

Originally Posted by Mumps1

I've changed the code to a Worksheet_BeforeDoubleClick event. This simply means that now you have to double click the cell in column A instead of just selecting it.

Please Login or Register  to view this content.

If you use this version, you simply right click the cell:

Please Login or Register  to view this content.

Take your pick!

sorry for the late reply. had a very busy week. did not get time to check till today.
this macro has become more effective now. could you please add some additional code to stop it from throwing an error when an empty cell is called or when a cell with no file to be linked is called.

now this is way better. but can you modify the code so that the cell that is not linked does not change the font color to red. As of now the font color of the cell is changing to red if the file is hyperlinked or not. i would like the color to change for only those cells who have successfully been hyperlinked.
thank you so much for the help. it was very difficult linking almost 1100 cells manually.

this code is perfect now. thank you so much Mumps1 for the help. i had made a same thread on 2 different forums before i posted here, but no replies yet.
thank you once again

done. thank you once again for all the help