VBA - Select range and add hyperlink

[John_vba]

Hi all,

So I have this excel file with a lot of different hyperlinks which paths need to be updated every quarter.
However I do not want to update all of them at the same time since they all have a different path. What I'd like to do is to (1) enter the range of the hyperlink I want to edit in an InputBox then (2) delete the hyperlink that's already in this cell and (3) Add a new hyperlink in this cell by enter a new path in an InputBox.

I managed to write something to complete steps 1 and 2 but then I don't know where to go from there ...

Sub test_range
Dim rng As Range

On Error Resume Next
  Set rng = Application.InputBox(prompt:="Enter range", Type:=8)
  If rng Is Nothing Then
    MsgBox "Cancelled"
  Else
    rng.Select
  End If
ActiveCell.Hyperlinks.Delete

' create a new hyperlink that you enter in an InputBox

End Sub

Thanks guys I already lost an entire afternoon at work ...

John

 

[John_vba]

anyone? I've got a deadline on this and I'm kind of stuck at the moment, any help would be appreciated! Thanks so much