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
    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
    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.

  • Not only are you allowed to post a link to anther forum, but, you MUST post links to all forums where you have posted the same question.

    That is in the rules to which you agreed when joining Ozgrid.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!