Excel variable for path

  • Thread starter Thread starter Esgrimidor
  • Start date Start date
That requires coding. Not sure if you know how to do that in Excel or not...

the resulting open would be "Y:\document\"&Range("A2")&"\proof.doc"
 
As Sean said, this takes a little coding to do, but I'll provide the code an
all you have to do is copy it, put it in the proper place and edit it to
match your worksheet and path setup. The comments in the code tell where you
have to change things.

To put this code in the proper place, open your workbook and RIGHT-click on
the name tab for the sheet where this is to be used and choose [View Code]
from the list that appears. That will open the VB Editor and present an
empty code module to you. Copy the code below and paste it into the module,
make any changes to the code that you need to and then close the VB Editor.

After you've done that, when you change the entry in the variable cell, it
will automatically rebuild the hyperlink in the other cell to point to your
document.

Here's the code:

Private Sub Worksheet_Change(ByVal Target As Range)
'change this to the address of the
'cell with %variable% in it
'make sure you have the $ symbols as shown
Const variableCell = "$D$9"
'change this to the address of
'the cell with the hyperlink
'to open your document in it
Const linkCell = "$F$9"
If Target.Address <> variableCell Then
Exit Sub
End If
'change the portions within " marks to
'form the left and right portions of the
'hyperlink when added to the entry in
'the variable cell
ActiveSheet.Hyperlinks.Add Anchor:=Range(linkCell), Address:= _
"C:\Users\" & Range(variableCell) & _
"\Documents\My Word Document.doc", TextToDisplay:= _
"Link to Word Document"
End Sub
 
Back
Top