Help Hyperlink to Folder With VBA Code

[mkoziol2]

I have done numerous searches and attempted different code myself I can't seem to get it to work though and I am hoping someone can help me out.

What I want is that when the code is executed it creates a folder for every value in column A and doesn't make repeats. Then Once this folder is created I want the value it came from to become a hyperlink to the new folder.

I have the code working just fine to create the folder and not make repeats. I can't figure out how to make it hyperlink itself though. Any help would be greatly appreciated.

 

[mkoziol2]

One more question, hopefully you can clear up. It's all working for the most part but if I close the file and reopen it and re-execute the code, as if I added a new file that I want a folder and link created for, all the previous ones lose their date code display and display the whole path of the hyperlink instead.

Anyway to stop this from happening?

It only happens if its re-run after closing. If I make a new entry such as test it will create the folder and link and keep the display test. however if I close it and reopen it and add test 1 the previous entry of test becomes its location as the display instead.

Thank you.

 

[p45cal]

Two things you could try:
1. Surround the line which adds a hyperlink with If..End if:
If Range("A" & i).Hyperlinks.Count = 0 Then
ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & i), Address:=dir
End If
2. or instead, enable the commented-out line:
'Range("A" & i).Hyperlinks.Delete

 

[mkoziol2]

Maybe this just isn't meant to happen......

If I do option 1 it doesn't do the directory showing like it was but it deletes all of my formatiing like centering and borders on these entries in Column A

If I do option 2 It doesn't create a proper link it makes the folder and tries to create a link to the folder name, which isn't a directory and wont work. If you go to edit hyperlink it just shows the entry as the location not an actual location on the computer.

 

[mkoziol2]

It seems it was storing some kind of memory of the hyperlinks. If I delete everything and then go with your first option code it will work but if I just left all the old links in place and changed the code then it acts up not quite sure why.

Thanks for all of your help.

 

[p45cal]

There is an additional argument:
ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & i), Address:=dir, TextToDisplay:=Range("A" & i).Value

Hyperlinks have their own format associated with them it seems:
http://www.excelforum.com/excel-gen...the-default-font-for-hyperlinks-in-excel.html
http://www.excelforum.com/excel-general/602719-hyperlink-font-format.html