hyperlink made easy???

  • Thread starter Thread starter index
  • Start date Start date
I

index

Is there an easy way in excel to simply click on a cell in excel,
instruct excel that you want to create a hyperlink and then simply
select the cell to link to in the workbook?

Alternatively does anyone know how to create a macro that will instruct
the user to enter two cell references which will then be automatically
linked to each other?

If you imagine you are dealing with the most simple excel user you
won't be far wrong!!!! Thanks for any help!
 
I tried doing sonething very similar to this once but was told it was
'almost' impossible and i got no further with it. Sorry! Although if
anyone can prove me wrong i'd still be interested in seeing if its
possible.
 
Hi

This is a quick start that can (and probably should) be refined for days:

Sub MakeLinks()
Dim R1 As Range, R2 As Range
Dim N1 As String, N2 As String

On Error Resume Next

Set R1 = Application.InputBox("Click cell 1", _
"Link between cells", _
Selection.Address, , , , , 8)
If R1 Is Nothing Then Exit Sub
Set R1 = R1(1)
N1 = InputBox("Decide a name for " & _
R1.Address & ":")
If N1 = "" Then Exit Sub

Set R2 = Application.InputBox("Click cell 2", _
"Link between cells", _
Selection.Address, , , , , 8)
If R2 Is Nothing Then Exit Sub
Set R2 = R2(1)
N2 = InputBox("Decide a name for " & _
R2.Address & ":")
If N2 = "" Then Exit Sub

ActiveWorkbook.Names.Add _
Name:=N1, RefersTo:="=" & _
R1.Parent.Name & "!" & _
R1.Address

ActiveWorkbook.Names.Add _
Name:=N2, RefersTo:="=" & _
R2.Parent.Name & "!" & _
R2.Address

R1.Parent.Hyperlinks.Add Anchor:=R1, _
Address:="", _
SubAddress:=N2, TextToDisplay:="Goto " & N2

R2.Parent.Hyperlinks.Add Anchor:=R2, _
Address:="", _
SubAddress:=N1, TextToDisplay:="Goto " & N1

End Sub
 
Thanks very much for that, you've almost made me very very happy -
however at present the link appears to work, ie that cells are linked
(and turned blue) but when i come to click on the first cell i get the
message "Reference is not valid"

Any idea how to fix it - i've been waiting months for a solution so i'm
crossing all my fingers and toes!!

Once again, many thanks
 
Hi

What's references is the name you enter in the "Name" prompt. If you enter a
name that already exists or a name that excel can't assign (like, you can't
assign the name "C4" to cell D3 because C4 is in use for cell C4) then
errors appear. The code should trap this after some refinement, but that's
more that I will put into a newsgroup posting. Until you fix that, enter
obvious, silly, unique names every time.

The reason for using names in hyperlinks is because you probably want the
references /links to follow when you insert and delete rows or columns.
 
If I understand you correctly by clicking on a cell to supply that address,
you would actually be working with a HYPERLINK Worksheet Function
rather than code. You should be able select the cell on the other
location while building your formula.

If it is a HYPERLINK Worksheet Function you are working with, I have
some examples in

Worksheets in VBA Coding and in Worksheet Formulas
http://www.mvps.org/dmcritchie/excel/sheets.htm#hyperlink

With Ctrl+K or right-click then hyperlink
you would choose place in this document and type in the cell
address if in same sheet, but you can't select the cell to fill it
in automatically.
 
i'm loving the hyperlink macro but i'd like to remove the 'name th
cell' element of the macro - is it possible to modify macro to simpl
link the cells without naming them?

Your help is, once again, much appreciated
 
Back
Top