N
nicks81
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
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