How to change hyperlinks is user selected range

mdocton

Board Regular
Joined
Nov 13, 2008
Messages
63
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
Code:
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
Code:
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.

Code:
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
 
Upvote 0
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 ...
 
Upvote 0
Many thanks for your help ... put me on the right lines and solved it now :)
This works for me;

Code:
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
 
Upvote 0

Forum statistics

Threads
1,222,014
Messages
6,163,392
Members
451,834
Latest member
tomtownson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top