Search Directory for Create Hyperlink of folder based on cell String

  • Hi,


    I searched on many websites but did not get similar things. I am sharing my thoughts. Hope someone helps me with the suggestion, sample file, or link.


    In my worksheet Cell, E9 to E100 has Some names. I want to make those names as hyperlinks.

    There are few folders in "E:\Names\ " having exact same name of E9 to E100 column ( E9:E100 is the column range).


    Now the facts:


    Now If the folder name matches the cell String then the hyperlink creating on that cell.

    If there is a folder which name is not matched with any cell string then nothing happens.

    If there is a cell string but no folder is matched exact same name in that directory then a folder will be created based on the name of the cell string along with the hyperlink.

    If any new name is add in cell E9 to E100 then automatically a folder will be created along with the hyperlink.


    Can it be done with VBA? If so, please.


    Thank you in advance.


  • Hi there, I hope this is what you mean?



    Let me know if it worked :)

  • Thanks, VincentNL


    Excellent Work except for the following problem.


    First, the code creates the hyperlink.

    Second, After Hyperlink the front E9:E100 changes Times New Roman to Calibri. (No Change in front)

    Third, Whenever the mouse courser comes to E9:E100 it changes to Hand selection and opens a popup massage. (No Hand tool, Press CTRL+Selection to open the hyperlink.

    Fourth, Gives an error (Here is the attachment)



    Hope you can solve it.

  • Hi there,


    Hope you can clarify

    1. Only no hyperlink should be created when no folder name matches any string right?

    2. That's fixable

    3. You don't want the popup message and change of tool icon?

    4. Can you attach a worksheet in what the code gives an error?

  • Hi. VincentNL


    Clarify

    1: Yes

    2. Front should be as it.

    3. Pop-up Message is not a problem. The problem is it stay always active. I want that Only when pressing down the CTRL of Keyboard the hyperlink active. If then select the cell it will give me a popup message whether I want to open the link. (Or any better suggestion: Appreciate)

    4 . Here is an attached file Hyper.xlsm as a sample.



    Best regards.

  • Hey Mamun,


    Thanks for adding the workbook, I hope my following changing are helpfull:


    1. Now if the value of the cell is empty nothing will happen, this will stop the error from accuring

    2. I added the style of the range to change to your description

    3. Added the code for automatically running the macro when values change in the range


    As for your next comment "I want that Only when pressing down the CTRL of Keyboard the hyperlink active", I got that working but it kept crashing the excel workbook every 10 seconds, so not sure about it yet



    Put this code in a module:


    Put this code in the sheet:

    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Not Intersect(Target, Range("E9:E100")) Is Nothing Then
    3. Call hyper
    4. End If
    5. End Sub