Hi everyone,

Hoping someone can help me. I'm trying to make a macro which will
prompt for user input (URL & link description), and create a hyperlink
from the input. I would like the final hyperlink created in the cell
selected by the user before activating the macro

This will be used for multiple links, so referencing the cell isn't a
practical workaround (as the hyperlink will change the next time
anybody creates a new link & replaces cells Z9 and Z10)

This is what I've managed so far:

Sub HyperLink()

'
' HyperLink Macro
'
Uservalue = InputBox("Paste URL:")
Range("Z9").Value = Uservalue
Uservalue2 = InputBox("Link Description:")
Range("Z10").Value = Uservalue2
'
End Sub
Sub Test()
'
' Test Macro
'

'
ActiveCell.FormulaR1C1 = "=HYPERLINK(R[-6]C[21],R[-5]C[21])"
Range("E15").Select
Selection.NumberFormat = "@"
Range("E15").Select
End Sub

The main problems with this are
a) I can't seem to make the new hyperlink in the user-selected cell,
rather it uses whichever cell I've used recording the macro
b) any historical cell-referenced links will update to the newer URL's
when Z9 & Z10 are updated

Can anyone offer any assistance?

Many thanks in advance,
Nick

Not with you on all of this but the highlighted line is referring to the
Cell that was selected when the Test Macro was run.
This may need to be set to what cell you need instead.

wrote in message
ps.com...
Hi everyone,

Hoping someone can help me. I'm trying to make a macro which will
prompt for user input (URL & link description), and create a hyperlink
from the input. I would like the final hyperlink created in the cell
selected by the user before activating the macro

This will be used for multiple links, so referencing the cell isn't a
practical workaround (as the hyperlink will change the next time
anybody creates a new link & replaces cells Z9 and Z10)

This is what I've managed so far:

Sub HyperLink()

'
' HyperLink Macro
'
Uservalue = InputBox("Paste URL:")
Range("Z9").Value = Uservalue
Uservalue2 = InputBox("Link Description:")
Range("Z10").Value = Uservalue2
'
End Sub
Sub Test()
'
' Test Macro
'

'
ActiveCell.FormulaR1C1 = "=HYPERLINK(R[-6]C[21],R[-5]C[21])" ' <=====
ActiveCell. This will place the Hyperlink to what cell was selected when
the Macro was run. If i am understanding correctly you may want to replace
ActiveCell with Range("Z9").FormulaR1C1
="=HYPERLINK(R[-6]C[21],R[-5]C[21])"
Range("E15").Select
Selection.NumberFormat = "@"
Range("E15").Select
End Sub

The main problems with this are
a) I can't seem to make the new hyperlink in the user-selected cell,
rather it uses whichever cell I've used recording the macro
b) any historical cell-referenced links will update to the newer URL's
when Z9 & Z10 are updated

Can anyone offer any assistance?

Many thanks in advance,
Nick

Hi Cory,

Thanks for the reply, I'm sorry I should have been clearer as to what
I'm trying to achieve.

Basically I want to have this macro linked to a button. The user could
then select a cell where they'd like a hyperlink, click the button (and
be prompted for the URL etc), and then have the link generated into the
cell they initally selected.

The problems with my macro above a

1) The 'final' cell is static... I need to add in something that takes
the user's selected cell, and uses that cell for the final output
2) The formula is referencing cells Z9 & Z10. So next time someone
updates these cells, the historic hyperlinks will change & all be the
same.

I was trying to do a macro (please don't laugh, I'm fairly new to this)
which selected the text from the user-input cells, and then copied &
pasted them into a Hyperlink formula. This didn't work for some reason,
possibly because I had to break out from the formula to select & copy
the required text.

any ideas gratefully received :)

If you wanted to add the Insert|Hyperlink style, you may be able to use
something like:

Option Explicit
Sub testme02()

Dim myURL As String
Dim myTextToDisplay As String

myURL = InputBox(prompt:="what's the url")
If Trim(myURL) = "" Then
Exit Sub
End If

If LCase(Left(myURL, 5)) < "http:" Then
myURL = "http://" & myURL
End If

myTextToDisplay = InputBox(prompt:="What to display", Default:=myURL)
If Trim(myTextToDisplay) = "" Then
Exit Sub
End If

With ActiveCell
.Hyperlinks.Delete
.Hyperlinks.Add anchor:=.Cells, _
Address:=myURL, TextToDisplay:=myTextToDisplay
End With

End Sub

I assumed that the links will be HTTP:// style. You may want to be more
specific.

But I think hitting ctrl-k would be simpler/safer.

wrote:

Hi everyone,

Hoping someone can help me. I'm trying to make a macro which will
prompt for user input (URL & link description), and create a hyperlink
from the input. I would like the final hyperlink created in the cell
selected by the user before activating the macro

This will be used for multiple links, so referencing the cell isn't a
practical workaround (as the hyperlink will change the next time
anybody creates a new link & replaces cells Z9 and Z10)

This is what I've managed so far:

Sub HyperLink()

'
' HyperLink Macro
'
Uservalue = InputBox("Paste URL:")
Range("Z9").Value = Uservalue
Uservalue2 = InputBox("Link Description:")
Range("Z10").Value = Uservalue2
'
End Sub
Sub Test()
'
' Test Macro
'

'
ActiveCell.FormulaR1C1 = "=HYPERLINK(R[-6]C[21],R[-5]C[21])"
Range("E15").Select
Selection.NumberFormat = "@"
Range("E15").Select
End Sub

The main problems with this are
a) I can't seem to make the new hyperlink in the user-selected cell,
rather it uses whichever cell I've used recording the macro
b) any historical cell-referenced links will update to the newer URL's
when Z9 & Z10 are updated

Can anyone offer any assistance?

Many thanks in advance,
Nick

--

Dave Peterson

Nick,
Is this enough for the user ?

Private Sub CommandButton4_Click()
Application.Dialogs(xlDialogInsertHyperlink).Show
End Sub

NickHK

wrote in message
ps.com...
Hi everyone,

Hoping someone can help me. I'm trying to make a macro which will
prompt for user input (URL & link description), and create a hyperlink
from the input. I would like the final hyperlink created in the cell
selected by the user before activating the macro

This will be used for multiple links, so referencing the cell isn't a
practical workaround (as the hyperlink will change the next time
anybody creates a new link & replaces cells Z9 and Z10)

This is what I've managed so far:

Sub HyperLink()

'
' HyperLink Macro
'
Uservalue = InputBox("Paste URL:")
Range("Z9").Value = Uservalue
Uservalue2 = InputBox("Link Description:")
Range("Z10").Value = Uservalue2
'
End Sub
Sub Test()
'
' Test Macro
'

'
ActiveCell.FormulaR1C1 = "=HYPERLINK(R[-6]C[21],R[-5]C[21])"
Range("E15").Select
Selection.NumberFormat = "@"
Range("E15").Select
End Sub

The main problems with this are
a) I can't seem to make the new hyperlink in the user-selected cell,
rather it uses whichever cell I've used recording the macro
b) any historical cell-referenced links will update to the newer URL's
when Z9 & Z10 are updated

Can anyone offer any assistance?

Many thanks in advance,
Nick

Thank you Dave & Nick for the replies, you've sorted out my problems!!

Many, many thanks
Nick