User Input into Hyperlinks
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
|