Find and Hyperlink - Make hyperlink to other sheet cells based on condition

[Dhamo]

Hi,


How to create hyperlink (to other cell in other sheet) in a single go?


In 'find and Replace', if we replace 'Temp01' to 'A20', It will change. Is there any similar way to 'Find and Hyperlink'

 

[Luke M]

When you do the find and replace, replace with a formula.

E.g.,

Find: Temp01

Replace: =HYPERLINK("[My book.xls]'My Sheet'!A20","Click me")


Another way to easily create hyperlinks is sumply dragging them:

http://blog.contextures.com/archives/2009/11/04/creating-excel-hyperlinks-is-a-drag/

 

[Dhamo]

Thanks Luke! Its working.!


I wanted to achieve this by a macro, so I have created the below code.

[pre]

Sub Find_Hyperlink()

i = 2
For i = 2 To 10
Worksheets("Sheet2").Select
If Cells(i, 1).Value = "values" Then
ActiveSheet.Cells(i, 1).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Sheet1!A5", TextToDisplay:="values"
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End If
Next
End Sub

[/pre]
The code is also working as expected, but I need to change the code every now and then since I have to hyperlink more than one cell. And I tried invoking refedit control. But I am getting 'Element not found' error. I have two questions here.

1. How to call user form from module? (I will call this module by creating new ribbon later on)

2. How to get rid of this error and use RefEdit control and let the user to select the hyperlink location?

 

[Luke M]

Hi Dhamo,


If you use the

Application.InputBox and use Type 8, you can get a cell reference. Modifiying your macro to allow user to select hyperlink destination:

[pre][code]Sub Find_Hyperlink()
Dim myCell As Range
Dim cAddress As String
i = 2
For i = 2 To 10
Worksheets("Sheet2").Select
If Cells(i, 1).Value = "values" Then
'New code block
Set myCell = Application.InputBox("Where should we go to?", "Hyperlink", Type:=8)
cAddress = "'" & myCell.Worksheet.Name & "'!" & myCell.Address

ActiveSheet.Cells(i, 1).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
cAddress, TextToDisplay:="values"
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End If
Next
End Sub

I wasn't sure whether all cells should link to same cell, or pick individually. If same cell, just need to move the 2 lines outside of the For...Next loop.

PS. Curious...why do you have the macro follow the hyperlink, and then continue through the loop? It will just keep bouncing around it would seem, selecting different cells. Cleaning things up a little, perhaps this is a better way?

Sub Find_Hyperlink()
Dim myCell As Range
Dim cAddress As String
Dim i As Long

For i = 2 To 10
With Worksheets("Sheet2&#34
If .Cells(i, 1).Value = "values" Then
'New code block
Set myCell = Application.InputBox("Where should we go to?", "Hyperlink", Type:=8)
cAddress = "'" & myCell.Worksheet.Name & "'!" & myCell.Address

.Hyperlinks.Add Anchor:=.Cells(i, 1), Address:="", SubAddress:= _
cAddress, TextToDisplay:="values"
End If
End With
Next
End Sub[/code][/pre]