Information about activecell before clicking on hyperlink
 

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

Information about activecell before clicking on hyperlink
 

Hi,

Try this,

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

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

End Sub

cheers
Andy

Mats Nilsson wrote:
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, Microsoft MVP - Excel
http://www.andypope.info

Information about activecell before clicking on hyperlink
 

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

--
ESI Update


"Andy Pope" wrote:

Hi,

Try this,

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

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

End Sub

cheers
Andy

Mats Nilsson wrote:
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, Microsoft MVP - Excel
http://www.andypope.info

Information about activecell before clicking on hyperlink
 

What did that code give you then?

Mats Nilsson wrote:
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, Microsoft MVP - Excel
http://www.andypope.info

Information about activecell before clicking on hyperlink
 

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

--
ESI Update


"Andy Pope" wrote:

What did that code give you then?

Mats Nilsson wrote:
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, Microsoft MVP - Excel
http://www.andypope.info

Information about activecell before clicking on hyperlink
 

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

Mats Nilsson wrote:
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, Microsoft MVP - Excel
http://www.andypope.info

Information about activecell before clicking on hyperlink
 

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
--
ESI Update


"Andy Pope" wrote:

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

Mats Nilsson wrote:
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, Microsoft MVP - Excel
http://www.andypope.info

Information about activecell before clicking on hyperlink
 

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

Mats Nilsson wrote:
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, Microsoft MVP - Excel
http://www.andypope.info