hyperlink to changing field depending on input

[Stephen]

Hi,

I have two fields in the first workbook where an user can enter two numbers.
I would like to create a hyperlink which takes the user in the second
workbook to a cell corresponding to the coordonates entered by the user. for
example if user enters X=10 and Y=5 the hyperlink will take him to cell J5.

Thanks!

 

[joel]

This code will put the hperlink at the activecell as specified in cells A1
and B1

RowNumber = Range("A1")
ColNumber = Range("B1")
Set HLink = Workbooks("book1.xls").Sheets("Sheet1").Cells(RowNumber,
ColNumber)

ActiveSheet.Hyperlinks.Add _
Anchor:=ActiveCell, _
Address:=HLink, _
TextToDisplay:=HLink.Address(external:=True)

 

[Stephen]

thanks for the reply but please bear with me some more.
i don't know anything about excel programming so i am not sure what to do
with the code you sent. how should i use it.

thanks again.

 

[joel]

This is the simpliest method

1) From worksheet enter Alt-F11 to get to VBA window
2) From VBA menu

Insert Module

3) Paste macro below from SUB .... End Sub
4) Return to worksheet by typing Alt F11
5) From worksheet menu make sure macros are enabled

Tools - Macro Security - Select Medium

If any of the higher setting was selected you have to save and exit all
workbooks. then open the workbook and when the menu comes up to enable
macros select yes.

6) Enter in A1 the row Number
Enter B1 the column Number
Select the cell where you want to hyperlink to be placed.

7) Then run macro to place hyperlink

Tools - Macro - Macro - CreateHyperlink

8) You can create a button or a shortcut key also to run macro. Try these
steps first.



Sub CreateHyperlink()
RowNumber = Range("A1")
ColNumber = Range("B1")
Set HLink = Workbooks("book1.xls").Sheets("Sheet1").Cells(RowNumber,
ColNumber)

ActiveSheet.Hyperlinks.Add _
Anchor:=ActiveCell, _
Address:=HLink, _
TextToDisplay:=HLink.Address(external:=True)
end sub

 

[Stephen]

one step closer, still not there though.
after i run the teps above I get the following hyperlink:
[book1.xls]Sheet2!$E$10

I have replaced Sheet1 with Sheet2 because i want the hyperlink to send me
in the 2nd workbook. But dont know how to get rid of the $.

thanks.

 

[joel]

Why do you want to get rid of the dollar signs???

RowNumber = Range("A1")
ColNumber = Range("B1")
Set HLink = Workbooks("book1.xls").Sheets("Sheet1").Cells(RowNumber,
RowNumber)
HAddr = HLink.Address(external:=True)
HAddr = Replace(HAddr, "$", "")

ActiveSheet.Hyperlinks.Add _
Anchor:=ActiveCell, _
Address:=HAddr, _
TextToDisplay:=HAddr

one step closer, still not there though.
after i run the teps above I get the following hyperlink:
[book1.xls]Sheet2!$E$10

I have replaced Sheet1 with Sheet2 because i want the hyperlink to send me
in the 2nd workbook. But dont know how to get rid of the $.

thanks.

This is the simpliest method

1) From worksheet enter Alt-F11 to get to VBA window
2) From VBA menu

Insert Module

3) Paste macro below from SUB .... End Sub
4) Return to worksheet by typing Alt F11
5) From worksheet menu make sure macros are enabled

Tools - Macro Security - Select Medium

If any of the higher setting was selected you have to save and exit all
workbooks. then open the workbook and when the menu comes up to enable
macros select yes.

6) Enter in A1 the row Number
Enter B1 the column Number
Select the cell where you want to hyperlink to be placed.

7) Then run macro to place hyperlink

Tools - Macro - Macro - CreateHyperlink

8) You can create a button or a shortcut key also to run macro. Try these
steps first.



Sub CreateHyperlink()
RowNumber = Range("A1")
ColNumber = Range("B1")
Set HLink = Workbooks("book1.xls").Sheets("Sheet1").Cells(RowNumber,
ColNumber)

ActiveSheet.Hyperlinks.Add _
Anchor:=ActiveCell, _
Address:=HLink, _
TextToDisplay:=HLink.Address(external:=True)
end sub

 

[Stephen]

because it won't follow the hyperlink, thought that was the caused but it
seems not.
i get an error "canno open the file".
anyway i will try to check it later, see whats the cause, now i dont have
time for it.

thanks anyway for the help. it was a big push forward.

cheers.

Why do you want to get rid of the dollar signs???

RowNumber = Range("A1")
ColNumber = Range("B1")
Set HLink = Workbooks("book1.xls").Sheets("Sheet1").Cells(RowNumber,
RowNumber)
HAddr = HLink.Address(external:=True)
HAddr = Replace(HAddr, "$", "")

ActiveSheet.Hyperlinks.Add _
Anchor:=ActiveCell, _
Address:=HAddr, _
TextToDisplay:=HAddr

one step closer, still not there though.
after i run the teps above I get the following hyperlink:
[book1.xls]Sheet2!$E$10

I have replaced Sheet1 with Sheet2 because i want the hyperlink to send me
in the 2nd workbook. But dont know how to get rid of the $.

thanks.

This is the simpliest method

1) From worksheet enter Alt-F11 to get to VBA window
2) From VBA menu

Insert Module

3) Paste macro below from SUB .... End Sub
4) Return to worksheet by typing Alt F11
5) From worksheet menu make sure macros are enabled

Tools - Macro Security - Select Medium

If any of the higher setting was selected you have to save and exit all
workbooks. then open the workbook and when the menu comes up to enable
macros select yes.

6) Enter in A1 the row Number
Enter B1 the column Number
Select the cell where you want to hyperlink to be placed.

7) Then run macro to place hyperlink

Tools - Macro - Macro - CreateHyperlink

8) You can create a button or a shortcut key also to run macro. Try these
steps first.



Sub CreateHyperlink()
RowNumber = Range("A1")
ColNumber = Range("B1")
Set HLink = Workbooks("book1.xls").Sheets("Sheet1").Cells(RowNumber,
ColNumber)

ActiveSheet.Hyperlinks.Add _
Anchor:=ActiveCell, _
Address:=HLink, _
TextToDisplay:=HLink.Address(external:=True)
end sub


:

thanks for the reply but please bear with me some more.
i don't know anything about excel programming so i am not sure what to do
with the code you sent. how should i use it.

thanks again.

:

This code will put the hperlink at the activecell as specified in cells A1
and B1

RowNumber = Range("A1")
ColNumber = Range("B1")
Set HLink = Workbooks("book1.xls").Sheets("Sheet1").Cells(RowNumber,
ColNumber)

ActiveSheet.Hyperlinks.Add _
Anchor:=ActiveCell, _
Address:=HLink, _
TextToDisplay:=HLink.Address(external:=True)


:

Hi,

I have two fields in the first workbook where an user can enter two numbers.
I would like to create a hyperlink which takes the user in the second
workbook to a cell corresponding to the coordonates entered by the user. for
example if user enters X=10 and Y=5 the hyperlink will take him to cell J5.

Thanks!