Auto Hyperlinks

[Rich H]

Hi,

Forgive me if this is easy but I'm not an Excel power user. I am
looking for a way to have a hyperlink automatically created based on the
input of a cell. Basically I have a column with data sheet numbers. I
would like a hyperlink to be automatically created each time I enter a
new data sheet number in a new cell. All the data sheets are stored in
the same location so the hyper links would be exactly the same except
for the data sheet number itself. Example (the "x"s represent the
variable data sheet number which is also the cell contents):

http://www.mydomain.com/datasheets/xxx-xx.pdf

Thanks,
Rich

 

[Jim Gordon MVP]

Hi,

I'm not sure if I grasp the entire way you are working, but if you check
Excel Help about the CONCATENATE worksheet function that could be they key
to what you need.

If not, could you provide some additional details about the steps you are
taking or would like to take?

-Jim Gordon
Mac MVP

Hi,

Forgive me if this is easy but I'm not an Excel power user. I am
looking for a way to have a hyperlink automatically created based on the
input of a cell. Basically I have a column with data sheet numbers. I
would like a hyperlink to be automatically created each time I enter a
new data sheet number in a new cell. All the data sheets are stored in
the same location so the hyper links would be exactly the same except
for the data sheet number itself. Example (the "x"s represent the
variable data sheet number which is also the cell contents):

http://www.mydomain.com/datasheets/xxx-xx.pdf

Thanks,
Rich

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

 

[Rich H]

Hi Jim,

Thanks for the response. With your direction I'm able to get halfway
there. I was able to create a new column with the proper hyperlink
address for each data sheet in it's row. But it's not all I need.

If I were to do all of this manually here's what would happen.
1) Enter the data sheet number in it's corresponding column (xx-xxx)
2) Highlight that same cell and create a hyperlink pointing to
"http://www.mydomain/datasheets/xx-xxx.pdf"
3) Enter the next data sheet number in the next row, same column, and
proceed to step 2, repeat, repeat, repeat...

What I want to happen automatically is that when I enter the data sheet
number and hit return the hyperlink is automatically applied to the data
sheet number I just entered.

If there is not built in ability in Excel to do this does anyone think I
could AppleScript it?

Thanks,
Rich

Hi,

I'm not sure if I grasp the entire way you are working, but if you check
Excel Help about the CONCATENATE worksheet function that could be they key
to what you need.

If not, could you provide some additional details about the steps you are
taking or would like to take?

-Jim Gordon
Mac MVP


Quoting from "Rich H" <[email protected]>, in article
(e-mail address removed), on [DATE:

Hi,

Forgive me if this is easy but I'm not an Excel power user. I am
looking for a way to have a hyperlink automatically created based on the
input of a cell. Basically I have a column with data sheet numbers. I
would like a hyperlink to be automatically created each time I enter a
new data sheet number in a new cell. All the data sheets are stored in
the same location so the hyper links would be exactly the same except
for the data sheet number itself. Example (the "x"s represent the
variable data sheet number which is also the cell contents):

http://www.mydomain.com/datasheets/xxx-xx.pdf

Thanks,
Rich

 

[JE McGimpsey]

What I want to happen automatically is that when I enter the data sheet
number and hit return the hyperlink is automatically applied to the data
sheet number I just entered.

One way:

Put this in your worksheet code module (CTRL- or right-click the
worksheet tab and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Const csENTRY_RANGE As String = "A1:A100"
Const csURI_TEMPLATE As String = _
"http://www.mydomain/datasheets/xx-xxx.pdf"

Dim rENTRY As Range
Dim rCell As Range
Dim sURI As String

Set rENTRY = Intersect(Range(csENTRY_RANGE), Target.Cells)
If Not rENTRY Is Nothing Then
For Each rCell In rENTRY
With rCell
.Hyperlinks.Delete
If .Text Like "##-###" Then
sURI = Application.Substitute( _
csURI_TEMPLATE, "xx-xxx", .Text)
.Parent.Hyperlinks.Add _
Anchor:=.Cells, _
Address:=sURI, _
TextToDisplay:=.Text
End If
End With
Next rCell
End If
End Sub

Now when you enter a value like 12-345 in a cell from A1:A100 (change
the range in csENTRY_RANGE), a hyperlink to your datasheet will be
created.

 

[Rich H]

Thanks! That worked. Two final questions (hopefully). I actually have
to similar columns, each pointing to a different hyperlinked directory
(instructionsheets instead of datasheets). To achieve the same ability
in the other column do I create a whole other script or do I modify the
same one? Also, is there a way to enable and disable the script when
needed and not needed?

Thanks,
Rich

 

[JE McGimpsey]

To achieve the same ability in the other column do I create a whole
other script or do I modify the same one?

You can only have one procedure for each event, so you should modify
that code.

Also, is there a way to enable and disable the script when needed and
not needed?

There are a wide variety of ways that this could be done. What might
indicate to XL that the event macro is needed or not needed?