How to change hyperlinks is user selected range

[mdocton]

Hi,
I am am amatuer trying to write a macro that will change the hyperlink paths in a spreadsheet in a range selected by the user (e.g. just those in L2:L3) ... e.g. here a folder of destinatiion documents have been moved.
I have worked out how to change the hyperlinks in a FIXED range.
I have worked out how to get a user-selected (VARIABLE) range,
but my limited knowledge has prevented me from combining the two.

The code I use for a fixed range L2:L3 is

Sub ChangeHyperlink()

Dim PathToChangeFrom As Variant
Dim PathToChangeTo As Variant
Dim str As String

PathToChangeFrom = InputBox("Input path to be changed")
PathToChangeTo = InputBox("Input new path")

For Each Cell In Range("L2:L3")
  On Error Resume Next
  str = Cell.Hyperlinks(1).Address
  Cell.Hyperlinks(1).Address = Replace(str, PathToChangeFrom, PathToChangeTo, , , vbTextCompare)
Next

End Sub

The code I use to get the user-selected range is

Dim rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Select Range" '"KutoolsforExcel9"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

I'd be grateful for help of how to change the hyperlinks (as per first code) just within the user-selected variable range (second code).
Or any alternative

Many thanks
Mark

 

[Jeffrey Mahoney]

Does this work for you? The user selects a range before invoking the macro, inputs the two paths, and there you go.

Of course if it were me, I would have the path to be changed and the new path in cells and have the macro read those cells for the values.

Sub ChangeHyperlink()

Dim PathToChangeFrom As Variant
Dim PathToChangeTo As Variant
Dim str As String
Dim Cel As Range

PathToChangeFrom = InputBox("Input path to be changed")
PathToChangeTo = InputBox("Input new path")

For Each Cel In Selection
  On Error Resume Next
  str = Cel.Hyperlinks(1).Address
  Cell.Hyperlinks(1).Address = Replace(str, PathToChangeFrom, PathToChangeTo, , , vbTextCompare)
Next Cel

End Sub

 

[mdocton]

Many thanks for your reply and I've tried your suggestion.

It read str as ..\Hyperlinks\DOC2.txt
PathToChangeFrom as Hyperlinks
PathToChangeTo as Hyperlinks\Dir A\

but it doesn't change the existing hyperlink ...

 

[mdocton]

Many thanks for your help ... put me on the right lines and solved it now
This works for me;

Sub ChangeHyperlink ()
 
Dim PathToChangeFrom As String
Dim PathToChangeTo As String
Dim rng As Range
Dim cell As Range
Dim str As String
 
PathToChangeFrom = InputBox("Input path to be changed")
PathToChangeTo = InputBox("Input new path")
Set rng = Selection
     For Each cell In rng
     On Error Resume Next
        str = cell.Hyperlinks(1).Address
        cell.Hyperlinks(1).Address = Replace(str, PathToChangeFrom, PathToChangeTo, , , vbTextCompare)
     Next cell
    
End Sub