Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink with Code
is there a way to have a hyperlink that also can then run a subroutine.
The hyperlink is from one worksheet to another in the same workbook. After the hyperlink is selected I would then like to call my subroutine that copys data to the destination worksheet. I would prefer to use a hyperlink as opposed to a command button because the cell(s) that will have the hyperlinks are small and I don't want all these very small command buttons. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink with Code
You can get the sheet name frok the hyperlink rather than actual activating
the hyperlink. See code below Sub test() Set linkcell = Sheets("Sheet1").Range("D1") DestAddr = linkcell.Hyperlinks.Item(1).SubAddress If InStr(DestAddr, "!") 0 Then ShtName = Left(DestAddr, InStr(DestAddr, "!") - 1) Set DestAddr = Sheets(ShtName) Else Set DestAddr = ActiveSheet End If End Sub "James" wrote: is there a way to have a hyperlink that also can then run a subroutine. The hyperlink is from one worksheet to another in the same workbook. After the hyperlink is selected I would then like to call my subroutine that copys data to the destination worksheet. I would prefer to use a hyperlink as opposed to a command button because the cell(s) that will have the hyperlinks are small and I don't want all these very small command buttons. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink with Code
Say we have a hyperlink on Sheet1 that jumps to Sheet2. Say we want to run
code that will copy A1 thru A3 from Sheet1 to Sheet2 cell B9 when the jump is made. Put the following event macro in the worksheet code area of Sheet1: Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Sheets("Sheet1").Range("A1:A3").Copy Sheets("Sheet2").Range("B9") End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200903 "James" wrote: is there a way to have a hyperlink that also can then run a subroutine. The hyperlink is from one worksheet to another in the same workbook. After the hyperlink is selected I would then like to call my subroutine that copys data to the destination worksheet. I would prefer to use a hyperlink as opposed to a command button because the cell(s) that will have the hyperlinks are small and I don't want all these very small command buttons. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink with Code
Would you know how I can determine which hyperlink was selected?
I have multiple hyperlinks on a worksheet all jumping to the same destination worksheet, but each one should be passing different parameters to the subroutine that does the copy. Thanks. "Gary''s Student" wrote: Say we have a hyperlink on Sheet1 that jumps to Sheet2. Say we want to run code that will copy A1 thru A3 from Sheet1 to Sheet2 cell B9 when the jump is made. Put the following event macro in the worksheet code area of Sheet1: Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Sheets("Sheet1").Range("A1:A3").Copy Sheets("Sheet2").Range("B9") End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200903 "James" wrote: is there a way to have a hyperlink that also can then run a subroutine. The hyperlink is from one worksheet to another in the same workbook. After the hyperlink is selected I would then like to call my subroutine that copys data to the destination worksheet. I would prefer to use a hyperlink as opposed to a command button because the cell(s) that will have the hyperlinks are small and I don't want all these very small command buttons. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink with Code
Good question!
If you put: MsgBox Target.Parent.Address It will display the cell address in Sheet1 that was clicked (where we came from) You can then put some If's or a Select Case statement in the code to do different thing based upon which hyperlink was clicked. -- Gary''s Student - gsnu200903 "James" wrote: Would you know how I can determine which hyperlink was selected? I have multiple hyperlinks on a worksheet all jumping to the same destination worksheet, but each one should be passing different parameters to the subroutine that does the copy. Thanks. "Gary''s Student" wrote: Say we have a hyperlink on Sheet1 that jumps to Sheet2. Say we want to run code that will copy A1 thru A3 from Sheet1 to Sheet2 cell B9 when the jump is made. Put the following event macro in the worksheet code area of Sheet1: Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Sheets("Sheet1").Range("A1:A3").Copy Sheets("Sheet2").Range("B9") End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200903 "James" wrote: is there a way to have a hyperlink that also can then run a subroutine. The hyperlink is from one worksheet to another in the same workbook. After the hyperlink is selected I would then like to call my subroutine that copys data to the destination worksheet. I would prefer to use a hyperlink as opposed to a command button because the cell(s) that will have the hyperlinks are small and I don't want all these very small command buttons. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink with Code
Put the data onto the sheet before you move to the new sheet using this event
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) DestAddr = Target.SubAddress If InStr(DestAddr, "!") 0 Then ShtName = Left(DestAddr, InStr(DestAddr, "!") - 1) Set DestAddr = Sheets(ShtName) Else Set DestAddr = ActiveSheet End If End Sub if yo are writing data to a diffferent location then the Target locattion then use my previous code Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Range("A1:B5").Copy _ deestination:=Target.SubAddress End Sub "James" wrote: Would you know how I can determine which hyperlink was selected? I have multiple hyperlinks on a worksheet all jumping to the same destination worksheet, but each one should be passing different parameters to the subroutine that does the copy. Thanks. "Gary''s Student" wrote: Say we have a hyperlink on Sheet1 that jumps to Sheet2. Say we want to run code that will copy A1 thru A3 from Sheet1 to Sheet2 cell B9 when the jump is made. Put the following event macro in the worksheet code area of Sheet1: Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Sheets("Sheet1").Range("A1:A3").Copy Sheets("Sheet2").Range("B9") End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200903 "James" wrote: is there a way to have a hyperlink that also can then run a subroutine. The hyperlink is from one worksheet to another in the same workbook. After the hyperlink is selected I would then like to call my subroutine that copys data to the destination worksheet. I would prefer to use a hyperlink as opposed to a command button because the cell(s) that will have the hyperlinks are small and I don't want all these very small command buttons. Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink with Code
That did the tirck. Thanks.
"Gary''s Student" wrote: Good question! If you put: MsgBox Target.Parent.Address It will display the cell address in Sheet1 that was clicked (where we came from) You can then put some If's or a Select Case statement in the code to do different thing based upon which hyperlink was clicked. -- Gary''s Student - gsnu200903 "James" wrote: Would you know how I can determine which hyperlink was selected? I have multiple hyperlinks on a worksheet all jumping to the same destination worksheet, but each one should be passing different parameters to the subroutine that does the copy. Thanks. "Gary''s Student" wrote: Say we have a hyperlink on Sheet1 that jumps to Sheet2. Say we want to run code that will copy A1 thru A3 from Sheet1 to Sheet2 cell B9 when the jump is made. Put the following event macro in the worksheet code area of Sheet1: Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Sheets("Sheet1").Range("A1:A3").Copy Sheets("Sheet2").Range("B9") End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200903 "James" wrote: is there a way to have a hyperlink that also can then run a subroutine. The hyperlink is from one worksheet to another in the same workbook. After the hyperlink is selected I would then like to call my subroutine that copys data to the destination worksheet. I would prefer to use a hyperlink as opposed to a command button because the cell(s) that will have the hyperlinks are small and I don't want all these very small command buttons. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB Code for hyperlink | Excel Programming | |||
Hyperlink to run code | Excel Discussion (Misc queries) | |||
Need to add hyperlink using VBA code | Excel Programming | |||
Hyperlink to code | Excel Discussion (Misc queries) | |||
Hyperlink via code | Excel Programming |