Information about activecell before clicking on hyperlink

[Guest]

Hi,
In my model I have inserted hyperlinks with subadresses in the same sheet
(or other sheets in the workbook).
When the user clicks on a hyperlink a coderoutine will start (the event
Worksheet_FollowHyperlink). In that routine I need to know the activecell
before the targetcell in the hyperlink is activated.
Thanks in advance for any help or comment.

Kind Regards
Mats
ESI Update

 

[Andy Pope]

Hi,

Try this,

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

MsgBox Target.Range.Parent.Name & "!" & Target.Range.Address

End Sub

cheers
Andy

 

[Guest]

Hi Andy,
Thanks for your answer but i didn´t get the activecell before the user
clicks on the hyperlink.
The parent = the worksheet name.
/Mats

 

[Andy Pope]

What did that code give you then?

Hi Andy,
Thanks for your answer but i didn´t get the activecell before the user
clicks on the hyperlink.
The parent = the worksheet name.
/Mats

 

[Guest]

Hi,
I got the sheetname and the Range where you click on the hyperlink (the
anchor).
In this Case "Resultatbudget!$L$1".


I would like to get the cell which was selected before you click on the
hyperlink.
e.g "Resultatbudget!$N$30".
/Mats

 

[Andy Pope]

Then you will need to store the previous active cell in a variable as
the cell could be any one the cells on the sheet.

Add a private variable to the sheet module,

Private m_rngACell as range


The add code to the SelectionChange event,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
set m_rnacell = target
End Sub

The in the FollowHyperlink event
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
MsgBox m_rngACell.Parent.Name & "!" & m_rngACell.Address
End Sub

Cheers
Andy

 

[Guest]

Hi,

I think there will be problems to store information in a variable when VBA
isn´t active until next selection change. Maybe a static variable could work.

It´s possible to store the information in cells in the worksheet. i think I
will use a code like this.
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Cells(1, 1).Value = ActiveSheet.Cells(2, 1).Value
ActiveSheet.Cells(2, 1).Value = Target.Address
rngAcellpr = ActiveSheet.Cells(1, 1).Value
rngAcell = ActiveSheet.Cells(2, 1).Value"

Thanks for your help.
Mats

 

[Andy Pope]

If your worried about the variable losing it contents then use a named
range to store the information in.