Position hyperlink in Excel
 

I'd like to copy a cell value next to hyperlink cell when I click the
hyperlink in Excel. Here is my code and I thought "ActiveCell" will
present the cell contains hyperlink. But it's not, the ActiveCell
points to A1 automatically. How do I point the cell contains
hyperlink? Thank you.

Here is my code:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal
Target As Hyperlink)

ActiveSheet.Cells(2, 2) = ActiveCell.Offset(0, 1).Value

End Sub

Position hyperlink in Excel
 

Hi
you may put the following macro in your worksheet module (the worksheet
of your hyperlink):

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim source_value
source_value = Target.Parent.Value
Target.parent.offset(0,1).value = source_value
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany


QQExcel wrote:
I'd like to copy a cell value next to hyperlink cell when I click the
hyperlink in Excel. Here is my code and I thought "ActiveCell" will
present the cell contains hyperlink. But it's not, the ActiveCell
points to A1 automatically. How do I point the cell contains
hyperlink? Thank you.

Here is my code:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal
Target As Hyperlink)

ActiveSheet.Cells(2, 2) = ActiveCell.Offset(0, 1).Value

End Sub

Position hyperlink in Excel
 

That's it! Target.parent.vale

Thanks! Frank!!!


"Frank Kabel" wrote in message ...
Hi
you may put the following macro in your worksheet module (the worksheet
of your hyperlink):

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim source_value
source_value = Target.Parent.Value
Target.parent.offset(0,1).value = source_value
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany


QQExcel wrote:
I'd like to copy a cell value next to hyperlink cell when I click the
hyperlink in Excel. Here is my code and I thought "ActiveCell" will
present the cell contains hyperlink. But it's not, the ActiveCell
points to A1 automatically. How do I point the cell contains
hyperlink? Thank you.

Here is my code:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal
Target As Hyperlink)

ActiveSheet.Cells(2, 2) = ActiveCell.Offset(0, 1).Value

End Sub