Hyperlink with Code

[James]

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

 

[Joel]

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

 

[Gary''s Student]

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

 

[James]

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]

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.

 

[Joel]

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]

That did the tirck. Thanks.

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.