How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Richard Your Own Question

Richard
Richard, Consultant
Category: Programming
Satisfied Customers: 31122
Experience:  Programming and Network consultancy for 15+ years
32989067
Type Your Programming Question Here...
Richard is online now
A new question is answered every 9 seconds

I am working with Microsoft Excel 2013 for PC and using a

This answer was rated:

I am working with Microsoft Excel 2013 for PC and using a SHARED document.
I have created a database where I would like to copy and past a File Path into any cell in Column A and have that File Path automatically converted into a hyperlink in the same cell.
While in SHARED documents it is not possible to right click and select "Insert Hyperlink", it is possible to use a formula (e.g. =hyperlink(link location,friendly name). The formula is not a viable option since would require either the user to enter the formula each time OR to enter the file path in one cell and have the hyperlink be generated in another cell. Neither of which is desirable.
Therefore, I have turned to VBA script to accomplish this task. I have successfully made this work using the Hyperlinks.Add function in an UNSHARED document, but the script does not work in a SHARED document.
I have attempted to use range.formula = "=Hyperlink... without success.
I will be looking for the full script to make this work (i.e. Private Sub...through...End Sub)

Hi there and welcome

this is one of the issues in Excel that as soon as it becomes shared you lose the Hyperlink functionality

But using a replace statement should work for you

Use this please

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 and Not Target.Value = "" Then

target.addreess = "=hyperlink(" &target.value & "," & target.value & ")"

end if

end sub

This should then write the formula for you once the path has been entered in

Let me know please how you go.

Customer: replied 9 days ago.
Thanks for the help Richard,I tested out the scripts in an UNSHARED document and received the following error:Compile Error:
Assignment to constant not permittedAny suggestions?!

can you use this then instead please

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Not Target.Value = "" Then
Target.Value2 = "=hyperlink(""" & Target.Value & """,""" & Target.Value & """)"
End If
End Sub

Customer: replied 9 days ago.
I tired that and now I get a different error message.Run-time error '13':
Type mismatch

Ok, the code is working on my test sheets

can you upload the file to www.wikisend.com

Then give me the download link please

Customer: replied 9 days ago.
Here is the link;Http://wikisend.com/download/945576/J_Sharp_VBA_Hyperlink.xlsm

ok, you didnt have it as in my message

Copy and paste this

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 And Not Target.Value = "" Then
Target.Value2 = "=hyperlink(""" & Target.Value & """,""" & Target.Value & """)"
End If
End Sub

You need the """ around them

how did you go with this?

Customer: replied 9 days ago.
Sorry about that typo. After making the correction to include double quotes "" the script worked!!Could I ask one quick question about your use of target.value2? Is this a way to have the input (in this case the file path) being defined at target.value (e.g. target.value1) and the output being defined as target.value2? I never would have thought to do this.Many thanks and happy Thanksgiving,
Jared

No, but another trick is to make a global variable and use this.

So

Dim oval

and in the SelectionChange Event

Public Sub Worksheet_SelectionChange(ByVal Target As Range) oval = Target.Value End Sub

and in your Worksheet_Change event set

old_value = oval

Then oval is the value before the change.

Great it is working Jared, you too a happy Thanksgiving and please remember the service rating via one of the stars at the top then submit

Thank you

Richard

Richard and other Programming Specialists are ready to help you

Related Programming Questions