Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
hyperlink to changing field depending on input
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
hyperlink to changing field depending on input
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(RowN umber, ColNumber) ActiveSheet.Hyperlinks.Add _ Anchor:=ActiveCell, _ Address:=HLink, _ TextToDisplay:=HLink.Address(external:=True) "Stephen" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
hyperlink to changing field depending on input
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" wrote: 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(RowN umber, ColNumber) ActiveSheet.Hyperlinks.Add _ Anchor:=ActiveCell, _ Address:=HLink, _ TextToDisplay:=HLink.Address(external:=True) "Stephen" wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
hyperlink to changing field depending on input
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(RowN umber, ColNumber) ActiveSheet.Hyperlinks.Add _ Anchor:=ActiveCell, _ Address:=HLink, _ TextToDisplay:=HLink.Address(external:=True) end sub "Stephen" wrote: 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" wrote: 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(RowN umber, ColNumber) ActiveSheet.Hyperlinks.Add _ Anchor:=ActiveCell, _ Address:=HLink, _ TextToDisplay:=HLink.Address(external:=True) "Stephen" wrote: 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
hyperlink to changing field depending on input
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" wrote: 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(RowN umber, ColNumber) ActiveSheet.Hyperlinks.Add _ Anchor:=ActiveCell, _ Address:=HLink, _ TextToDisplay:=HLink.Address(external:=True) end sub "Stephen" wrote: 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" wrote: 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(RowN umber, ColNumber) ActiveSheet.Hyperlinks.Add _ Anchor:=ActiveCell, _ Address:=HLink, _ TextToDisplay:=HLink.Address(external:=True) "Stephen" wrote: 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
hyperlink to changing field depending on input
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(RowN umber, RowNumber) HAddr = HLink.Address(external:=True) HAddr = Replace(HAddr, "$", "") ActiveSheet.Hyperlinks.Add _ Anchor:=ActiveCell, _ Address:=HAddr, _ TextToDisplay:=HAddr "Stephen" wrote: 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" wrote: 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(RowN umber, ColNumber) ActiveSheet.Hyperlinks.Add _ Anchor:=ActiveCell, _ Address:=HLink, _ TextToDisplay:=HLink.Address(external:=True) end sub "Stephen" wrote: 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" wrote: 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(RowN umber, ColNumber) ActiveSheet.Hyperlinks.Add _ Anchor:=ActiveCell, _ Address:=HLink, _ TextToDisplay:=HLink.Address(external:=True) "Stephen" wrote: 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
hyperlink to changing field depending on input
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. "joel" wrote: 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(RowN umber, RowNumber) HAddr = HLink.Address(external:=True) HAddr = Replace(HAddr, "$", "") ActiveSheet.Hyperlinks.Add _ Anchor:=ActiveCell, _ Address:=HAddr, _ TextToDisplay:=HAddr "Stephen" wrote: 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" wrote: 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(RowN umber, ColNumber) ActiveSheet.Hyperlinks.Add _ Anchor:=ActiveCell, _ Address:=HLink, _ TextToDisplay:=HLink.Address(external:=True) end sub "Stephen" wrote: 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" wrote: 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(RowN umber, ColNumber) ActiveSheet.Hyperlinks.Add _ Anchor:=ActiveCell, _ Address:=HLink, _ TextToDisplay:=HLink.Address(external:=True) "Stephen" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Increasing number depending on input | Excel Worksheet Functions | |||
Background Colour Depending On Input | Excel Discussion (Misc queries) | |||
Function or number depending on input | Excel Worksheet Functions | |||
Value or formula in cell depending on input... | Excel Programming | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming |