Return to last position

[Martin]

Hi,

I have looked up several postings on last cell and screen positions but cant
seem to get anything to work. Here is a description of my spreadsheet:

I have lots of rows of data wuth Jan to Dec headings and the years 2001 to
2020 as rows. I have this on several series of data and have recorded macros
to hide each year depending on what the users clicks on. I have used
hyperlinks to run the macros:


Hyperlink code is:
If ActiveCell.Address = "$E$3" Then Call Hide2002

Here is the sub:
Sub Hide2002()
Range("12:12,29:29,46:46").Select
Selection.EntireRow.Hidden = True
Range([Go to last position the user was at]).Select
End Sub


My problem is that I want Excel to return to the last screen position or
cell that they clicked in. I can get this to work but unfortunately as the
hyperlink is the last cell they clicked in it returns to that cell. The
hyperlinks are at the top of the sheet, the user may have been in row 502 for
instance.

Any help would be greatly appreciated, I have been trying for hours now!

Martin

 

[OssieMac]

Hi Martin,

Instead of going to another cell to slect a hyperlink to start the macro,
what about changing to Double Click event in the cell to start macro. That
way you can save the address of the cell.

 

[Martin]

Thanks, good idea but there are many other hyperlinks on the sheet and i
wanted to try and keep the same look and feel for the user if possible

Hi Martin,

Instead of going to another cell to slect a hyperlink to start the macro,
what about changing to Double Click event in the cell to start macro. That
way you can save the address of the cell.

--
Regards,

OssieMac

Hi,

I have looked up several postings on last cell and screen positions but cant
seem to get anything to work. Here is a description of my spreadsheet:

I have lots of rows of data wuth Jan to Dec headings and the years 2001 to
2020 as rows. I have this on several series of data and have recorded macros
to hide each year depending on what the users clicks on. I have used
hyperlinks to run the macros:


Hyperlink code is:
If ActiveCell.Address = "$E$3" Then Call Hide2002

Here is the sub:
Sub Hide2002()
Range("12:12,29:29,46:46").Select
Selection.EntireRow.Hidden = True
Range([Go to last position the user was at]).Select
End Sub


My problem is that I want Excel to return to the last screen position or
cell that they clicked in. I can get this to work but unfortunately as the
hyperlink is the last cell they clicked in it returns to that cell. The
hyperlinks are at the top of the sheet, the user may have been in row 502 for
instance.

Any help would be greatly appreciated, I have been trying for hours now!

Martin

 

[Mike H]

Martin,

maybe this

Public lastaddress As String
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
lastaddress = ActiveCell.Address
If ActiveCell.Address = "$E$3" Then Call Hide2002
End Sub


Sub Hide2002()
Range("12:12,29:29,46:46").EntireRow.Hidden = True
Range(lastaddress).Select
Stop
End Sub

Mike

Thanks, good idea but there are many other hyperlinks on the sheet and i
wanted to try and keep the same look and feel for the user if possible

Hi Martin,

Instead of going to another cell to slect a hyperlink to start the macro,
what about changing to Double Click event in the cell to start macro. That
way you can save the address of the cell.

--
Regards,

OssieMac

Hi,

I have looked up several postings on last cell and screen positions but cant
seem to get anything to work. Here is a description of my spreadsheet:

I have lots of rows of data wuth Jan to Dec headings and the years 2001 to
2020 as rows. I have this on several series of data and have recorded macros
to hide each year depending on what the users clicks on. I have used
hyperlinks to run the macros:


Hyperlink code is:
If ActiveCell.Address = "$E$3" Then Call Hide2002

Here is the sub:
Sub Hide2002()
Range("12:12,29:29,46:46").Select
Selection.EntireRow.Hidden = True
Range([Go to last position the user was at]).Select
End Sub


My problem is that I want Excel to return to the last screen position or
cell that they clicked in. I can get this to work but unfortunately as the
hyperlink is the last cell they clicked in it returns to that cell. The
hyperlinks are at the top of the sheet, the user may have been in row 502 for
instance.

Any help would be greatly appreciated, I have been trying for hours now!

Martin

 

[OssieMac]

Hi Mike,

Perhaps I am completely missing something here?
You are saving lastaddress = ActiveCell.Address
and then testing If ActiveCell.Address = "$E$3"
Surely lastaddress is going to be "$E$3"

To Martin,

You said that the Hyperlinks are all at the top of the sheet. Does that mean
that they are out of the range that you would be likely to want to return to?
If so, you might like to try saving the last selected cell (if outside the
Hyperlink area) like the following. Doesn't matter that it always gets saved
whether or not you actually click a hyperlink but when you do click a
hyperlink you get the last selected cell before selecting the Hyperlink.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim isect As Object

'Testing if in first 3 rows of worksheet
Set isect = Application.Intersect(Target, Range("1:3"))

If isect Is Nothing Then
'Selected cell not in first 3 rows (Hyperlink Area)
'Therefore save the ActiveCell address
'at any spare cell on the worksheet.
Range("K1") = ActiveCell.Address
End If

End Sub

Sub Hide2002()

Dim strRngK1 As String

strRngK1 = Range("K1")

Range("12:12,29:29,46:46").Select
Selection.EntireRow.Hidden = True

Range(strRngK1).Select

End Sub

--
Regards,

OssieMac

Martin,

maybe this

Public lastaddress As String
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
lastaddress = ActiveCell.Address
If ActiveCell.Address = "$E$3" Then Call Hide2002
End Sub


Sub Hide2002()
Range("12:12,29:29,46:46").EntireRow.Hidden = True
Range(lastaddress).Select
Stop
End Sub

Mike

Thanks, good idea but there are many other hyperlinks on the sheet and i
wanted to try and keep the same look and feel for the user if possible

Hi Martin,

Instead of going to another cell to slect a hyperlink to start the macro,
what about changing to Double Click event in the cell to start macro. That
way you can save the address of the cell.

--
Regards,

OssieMac


:

Hi,

I have looked up several postings on last cell and screen positions but cant
seem to get anything to work. Here is a description of my spreadsheet:

I have lots of rows of data wuth Jan to Dec headings and the years 2001 to
2020 as rows. I have this on several series of data and have recorded macros
to hide each year depending on what the users clicks on. I have used
hyperlinks to run the macros:


Hyperlink code is:
If ActiveCell.Address = "$E$3" Then Call Hide2002

Here is the sub:
Sub Hide2002()
Range("12:12,29:29,46:46").Select
Selection.EntireRow.Hidden = True
Range([Go to last position the user was at]).Select
End Sub


My problem is that I want Excel to return to the last screen position or
cell that they clicked in. I can get this to work but unfortunately as the
hyperlink is the last cell they clicked in it returns to that cell. The
hyperlinks are at the top of the sheet, the user may have been in row 502 for
instance.

Any help would be greatly appreciated, I have been trying for hours now!

Martin

 

[Mike H]

Hi,

The OP's post was vague when referring to 'Hyperlink code' and i assume the
means the hyperlink_follow event code. When this executes I'm suggesting the
activecell.address is captured in a Public variable; which may not be in E3,
and can be used later as a range to return to.

I think the issue is that the OP wants to 'return' to a cell which suggests
to me the snippet of code is incomplete because something must be happening
to move the cell selection.

Mike

Hi Mike,

Perhaps I am completely missing something here?
You are saving lastaddress = ActiveCell.Address
and then testing If ActiveCell.Address = "$E$3"
Surely lastaddress is going to be "$E$3"

To Martin,

You said that the Hyperlinks are all at the top of the sheet. Does that mean
that they are out of the range that you would be likely to want to return to?
If so, you might like to try saving the last selected cell (if outside the
Hyperlink area) like the following. Doesn't matter that it always gets saved
whether or not you actually click a hyperlink but when you do click a
hyperlink you get the last selected cell before selecting the Hyperlink.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim isect As Object

'Testing if in first 3 rows of worksheet
Set isect = Application.Intersect(Target, Range("1:3"))

If isect Is Nothing Then
'Selected cell not in first 3 rows (Hyperlink Area)
'Therefore save the ActiveCell address
'at any spare cell on the worksheet.
Range("K1") = ActiveCell.Address
End If

End Sub

Sub Hide2002()

Dim strRngK1 As String

strRngK1 = Range("K1")

Range("12:12,29:29,46:46").Select
Selection.EntireRow.Hidden = True

Range(strRngK1).Select

End Sub

--
Regards,

OssieMac

Martin,

maybe this

Public lastaddress As String
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
lastaddress = ActiveCell.Address
If ActiveCell.Address = "$E$3" Then Call Hide2002
End Sub


Sub Hide2002()
Range("12:12,29:29,46:46").EntireRow.Hidden = True
Range(lastaddress).Select
Stop
End Sub

Mike

Thanks, good idea but there are many other hyperlinks on the sheet and i
wanted to try and keep the same look and feel for the user if possible


:

Hi Martin,

Instead of going to another cell to slect a hyperlink to start the macro,
what about changing to Double Click event in the cell to start macro. That
way you can save the address of the cell.

--
Regards,

OssieMac


:

Hi,

I have looked up several postings on last cell and screen positions but cant
seem to get anything to work. Here is a description of my spreadsheet:

I have lots of rows of data wuth Jan to Dec headings and the years 2001 to
2020 as rows. I have this on several series of data and have recorded macros
to hide each year depending on what the users clicks on. I have used
hyperlinks to run the macros:


Hyperlink code is:
If ActiveCell.Address = "$E$3" Then Call Hide2002

Here is the sub:
Sub Hide2002()
Range("12:12,29:29,46:46").Select
Selection.EntireRow.Hidden = True
Range([Go to last position the user was at]).Select
End Sub


My problem is that I want Excel to return to the last screen position or
cell that they clicked in. I can get this to work but unfortunately as the
hyperlink is the last cell they clicked in it returns to that cell. The
hyperlinks are at the top of the sheet, the user may have been in row 502 for
instance.

Any help would be greatly appreciated, I have been trying for hours now!

Martin

 

[ShaneDevenshire]

Hi,

Is assume you have one block of code which all hyperlinks execute? If so
why not rewrite the code in a more standard way as shown below. No need to
move the cursor, no need to return because you are already there. No need to
have a lot of subroutine calls.

Sub MyHyperlinks()

With ActiveCell
If .Address = "$E$3" Then
Range("12:12,29:29,46:46").EntireRow.Hidden = True
Else If .Address = "$E$4"
'more code.....
End If
End With

End Sub

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire

Thanks, good idea but there are many other hyperlinks on the sheet and i
wanted to try and keep the same look and feel for the user if possible

Hi Martin,

Instead of going to another cell to slect a hyperlink to start the macro,
what about changing to Double Click event in the cell to start macro. That
way you can save the address of the cell.

--
Regards,

OssieMac

Hi,

I have looked up several postings on last cell and screen positions but cant
seem to get anything to work. Here is a description of my spreadsheet:

I have lots of rows of data wuth Jan to Dec headings and the years 2001 to
2020 as rows. I have this on several series of data and have recorded macros
to hide each year depending on what the users clicks on. I have used
hyperlinks to run the macros:


Hyperlink code is:
If ActiveCell.Address = "$E$3" Then Call Hide2002

Here is the sub:
Sub Hide2002()
Range("12:12,29:29,46:46").Select
Selection.EntireRow.Hidden = True
Range([Go to last position the user was at]).Select
End Sub


My problem is that I want Excel to return to the last screen position or
cell that they clicked in. I can get this to work but unfortunately as the
hyperlink is the last cell they clicked in it returns to that cell. The
hyperlinks are at the top of the sheet, the user may have been in row 502 for
instance.

Any help would be greatly appreciated, I have been trying for hours now!

Martin

 

[JLGWhiz]

Maybe you want to look at the PreviousSelections property and see if you
could work with that. It gives you the last four selections as an array, so
probably PreviousSelections(1) would give you the selection before last, or
in other words the selection before the hyperlink was clicked.

Thanks, good idea but there are many other hyperlinks on the sheet and i
wanted to try and keep the same look and feel for the user if possible

Hi Martin,

Instead of going to another cell to slect a hyperlink to start the macro,
what about changing to Double Click event in the cell to start macro. That
way you can save the address of the cell.

--
Regards,

OssieMac

Hi,

I have looked up several postings on last cell and screen positions but cant
seem to get anything to work. Here is a description of my spreadsheet:

I have lots of rows of data wuth Jan to Dec headings and the years 2001 to
2020 as rows. I have this on several series of data and have recorded macros
to hide each year depending on what the users clicks on. I have used
hyperlinks to run the macros:


Hyperlink code is:
If ActiveCell.Address = "$E$3" Then Call Hide2002

Here is the sub:
Sub Hide2002()
Range("12:12,29:29,46:46").Select
Selection.EntireRow.Hidden = True
Range([Go to last position the user was at]).Select
End Sub


My problem is that I want Excel to return to the last screen position or
cell that they clicked in. I can get this to work but unfortunately as the
hyperlink is the last cell they clicked in it returns to that cell. The
hyperlinks are at the top of the sheet, the user may have been in row 502 for
instance.

Any help would be greatly appreciated, I have been trying for hours now!

Martin

 

[Martin]

Thank you to all that replied and helped.

OssieMac - your code worked the best and returns to the last cell perfectly.

Thanks again,

Martin

Hi Mike,

Perhaps I am completely missing something here?
You are saving lastaddress = ActiveCell.Address
and then testing If ActiveCell.Address = "$E$3"
Surely lastaddress is going to be "$E$3"

To Martin,

You said that the Hyperlinks are all at the top of the sheet. Does that mean
that they are out of the range that you would be likely to want to return to?
If so, you might like to try saving the last selected cell (if outside the
Hyperlink area) like the following. Doesn't matter that it always gets saved
whether or not you actually click a hyperlink but when you do click a
hyperlink you get the last selected cell before selecting the Hyperlink.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim isect As Object

'Testing if in first 3 rows of worksheet
Set isect = Application.Intersect(Target, Range("1:3"))

If isect Is Nothing Then
'Selected cell not in first 3 rows (Hyperlink Area)
'Therefore save the ActiveCell address
'at any spare cell on the worksheet.
Range("K1") = ActiveCell.Address
End If

End Sub

Sub Hide2002()

Dim strRngK1 As String

strRngK1 = Range("K1")

Range("12:12,29:29,46:46").Select
Selection.EntireRow.Hidden = True

Range(strRngK1).Select

End Sub

--
Regards,

OssieMac

Martin,

maybe this

Public lastaddress As String
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
lastaddress = ActiveCell.Address
If ActiveCell.Address = "$E$3" Then Call Hide2002
End Sub


Sub Hide2002()
Range("12:12,29:29,46:46").EntireRow.Hidden = True
Range(lastaddress).Select
Stop
End Sub

Mike

Thanks, good idea but there are many other hyperlinks on the sheet and i
wanted to try and keep the same look and feel for the user if possible


:

Hi Martin,

Instead of going to another cell to slect a hyperlink to start the macro,
what about changing to Double Click event in the cell to start macro. That
way you can save the address of the cell.

--
Regards,

OssieMac


:

Hi,

I have looked up several postings on last cell and screen positions but cant
seem to get anything to work. Here is a description of my spreadsheet:

I have lots of rows of data wuth Jan to Dec headings and the years 2001 to
2020 as rows. I have this on several series of data and have recorded macros
to hide each year depending on what the users clicks on. I have used
hyperlinks to run the macros:


Hyperlink code is:
If ActiveCell.Address = "$E$3" Then Call Hide2002

Here is the sub:
Sub Hide2002()
Range("12:12,29:29,46:46").Select
Selection.EntireRow.Hidden = True
Range([Go to last position the user was at]).Select
End Sub


My problem is that I want Excel to return to the last screen position or
cell that they clicked in. I can get this to work but unfortunately as the
hyperlink is the last cell they clicked in it returns to that cell. The
hyperlinks are at the top of the sheet, the user may have been in row 502 for
instance.

Any help would be greatly appreciated, I have been trying for hours now!

Martin