Instant Hyperlink from Text

Little_Clubber

New Member
Joined
Nov 10, 2017
Messages
25
Hi all you wonderful people out there. I'm hoping one of you can help me slim down a spreadsheet.

The sheet we have has many columns of data, and for ease, one of those is a Hyperlink, based on another.
For Argument sake, let's say I put a value in A1.
In B1, I have the formula =HYPERLINK("F:\Quality\Start up\2020"&"\"&A1".pdf",A1)
So this creates a hyperlink to the pdf file with the same name, and displays the "friendly name" of the document, which is the exact same information as shown in cell A1.
So A1 and B1 show the same value, one is a hyperlink.

What I would prefer to do is type my data in A1 and upon pressing Tab, Excel automatically change it to the formula I need based on the data that was already entered.
Is this possible please?
Thank you
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
A VBA procedure can take care of that.
The code below determines if the changed cell is within column A. If that's the case, anything entered will be converted to the hyperlink formula.
The code goes into the worksheet module of the sheet to be affected. Right click on the sheet tab > View Code > paste code in main pane.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const cFormula As String = "=HYPERLINK(""F:\Quality\Start up\2020\@FILE@.pdf"", ""@FILE@"")"
    
    If Not Target.Count > 1 And Not Intersect(Target, Target.Parent.Columns("A")) Is Nothing Then
        Application.EnableEvents = False
        Target.Formula = Replace(cFormula, "@FILE@", Target.Value)
        Application.EnableEvents = True
    End If
End Sub
 
Solution

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
In case you don't need a formula but a real hyperlink try this ...

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Const cPath As String = "F:\Quality\Start up\2020\@FILE@"

    If Not Target.Count > 1 And Not Intersect(Target, Target.Parent.Columns("A")) Is Nothing Then
        Application.EnableEvents = False
        With Target.Parent.Hyperlinks.Add(Anchor:=Target, Address:=Replace(cPath, "@FILE@", Target.Value & ".pdf"))
            .TextToDisplay = Target.Value
            .ScreenTip = Target.Value & ".pdf"
        End With
        Application.EnableEvents = True
    End If
End Sub
 

Little_Clubber

New Member
Joined
Nov 10, 2017
Messages
25
A VBA procedure can take care of that.
The code below determines if the changed cell is within column A. If that's the case, anything entered will be converted to the hyperlink formula.
The code goes into the worksheet module of the sheet to be affected. Right click on the sheet tab > View Code > paste code in main pane.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   
    Const cFormula As String = "=HYPERLINK(""F:\Quality\Start up\2020\@FILE@.pdf"", ""@FILE@"")"
   
    If Not Target.Count > 1 And Not Intersect(Target, Target.Parent.Columns("A")) Is Nothing Then
        Application.EnableEvents = False
        Target.Formula = Replace(cFormula, "@FILE@", Target.Value)
        Application.EnableEvents = True
    End If
End Sub
Amazing! Thank you so much! Both of your suggested VBA codes do exactly what I need in terms of the hyperlink, thank you again.
I'm going to use the first one because should we change the folder structure at any point in time and move any existing documents, I can use Ctrl+H and change the path in the existing formulae to keep them working, which I wouldn't know how to do from your second VBA macro.
Thank you again!
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for the feedback.

I'm going to use the first one because should we change the folder structure at any point in time and move any existing documents, I can use Ctrl+H and change the path in the existing formulae to keep them working, which I wouldn't know how to do from your second VBA macro.
The macro below would do that, although unlike Ctrl H you cannot edit individual hyperlinks, that would have to be done via the cell context menu.
This macro changes all hyperlinks on the same worksheet pointing to files in the same folder.

VBA Code:
Private Sub ChangePathOfHyperlinkedFiles()

    Const cPathOld As String = "F:\Quality\Start up\2020\"
    Const cPathNew As String = "F:\Quality\2020\ArchivedFiles\"

    Dim oHl As Hyperlink
    For Each oHl In ActiveSheet.Hyperlinks
        If StrComp(Left(oHl.Address, (InStrRev(oHl.Address, "\"))), cPathOld, vbTextCompare) = 0 Then
            oHl.Address = Replace(oHl.Address, cPathOld, cPathNew)
        End If
    Next oHl
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,119,084
Messages
5,576,015
Members
412,694
Latest member
Deaf1Too
Top