VBA to link to folder on my network using the contents as a cell as part of the address.

[Medimatt]

Okay, I don't even know if this is possible as I have barely even used =HYPERLINK in the past let alone hyper link VBA code.

What I want to achieve is a link to a folder on my network, the final part of the address will be the same as the contents of another cell. So column A contains a list of course numbers, coulmnt T would need to link to w://medcourses/"contents of same row in column A" with the link name of 'Link to Cases' So clicking this link would just open that folder in windows explorer.

As I say, I am not sure this is possible, i can't even find anything close on the web so you guys are my last throw of the dice.

[Medimatt]

I am a little hopeful that I may have found the key to my solution, but I haven't been able to get it to work on my spreadsheet. I have found the following code online but can't quite translate it to solve my problem....

Option Explicit 
Private Sub Worksheet_Change(ByVal Target As  Range) 
    If Target.Column = 12 And Not Target.Value = "" Then 
        Application.EnableEvents = False 
        Hyperlinks.Add Target, Address:="http://www.google.com/search?q=" & Target.Value, TextToDisplay:="Click Here" 
        Application.EnableEvents = True 
    End If 
End Sub

[Arkadi]

Can you provide a worksheet with a row or 2 of data, and indicate the desired hyperlink for each?

Also, you posted a worksheet event as the example, if that is what you want, what exactly should trigger the event? Or do you just want a regular macro?

[Medimatt]

Can you provide a worksheet with a row or 2 of data, and indicate the desired hyperlink for each?

Also, you posted a worksheet event as the example, if that is what you want, what exactly should trigger the event? Or do you just want a regular macro?

Basically column A is a numeric reference to a course and columns b to s contain data relating to the course. Once data is entered into column A i would like column T to auto populate with a hyperlink to the folder on w: (i.e. w:\12345) 12345 being the folder name on w: and the contents of A

I can post a sample tomorrow when back at work.

Thanks

[Medimatt]

also, I would like the event triggered when the cell in column a is populated

Thanks

[kev_]

You may find this thread of interest.
https://www.excelforum.com/excel-gen...ml#post4661447

[kev_]

Here is an excellent source of valuable Excel and VBA advice - and this page deals specifically with hyperlinks

[kev_]

You do not require VBA to create links
I created folder medcourses on my D drive
If values in column A are folder names, this formula opens the folder specified in cell A2

=HYPERLINK("D:\medcourses\"&A2,"Link To Cases")

Had you wanted to open an Excel file specified in cell A2, then this would work

=HYPERLINK("D:\medcourses\"&A2&".xlsx","Link To Cases")

note:
I am not network savvy, but if your drive is not mapped, I believe the link string takes this pattern
\\MyServer\MyFolder\

[Medimatt]

Kev_, I guess I was trying to be too clever. I tried formulas originally but must have got something wrong and assumed i needed a macro. For the first time ever I can't actually think of anything that will improve my spreadsheet! Thank you very much.

[kev_]

You are welcome
Thanks for marking the thread as SOLVED