Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to last position
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to last position
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 "Martin" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to last position
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 "OssieMac" wrote: 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 "Martin" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to last position
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 "Martin" wrote: 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 "OssieMac" wrote: 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 "Martin" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to last position
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 "Mike H" wrote: 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 "Martin" wrote: 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 "OssieMac" wrote: 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 "Martin" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to last position
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 "OssieMac" wrote: 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 "Mike H" wrote: 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 "Martin" wrote: 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 "OssieMac" wrote: 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 "Martin" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to last position
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 "Martin" wrote: 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 "OssieMac" wrote: 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 "Martin" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to last position
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. "Martin" wrote: 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 "OssieMac" wrote: 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 "Martin" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to last position
Thank you to all that replied and helped.
OssieMac - your code worked the best and returns to the last cell perfectly. Thanks again, Martin "OssieMac" wrote: 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 "Mike H" wrote: 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 "Martin" wrote: 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 "OssieMac" wrote: 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 "Martin" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return position of 2nd, 3rd, ect occurrence of a character in a st | Excel Discussion (Misc queries) | |||
Return cursor to previous position | Excel Discussion (Misc queries) | |||
Formula to return cell position | Excel Worksheet Functions | |||
Return position of a sheet in a workbook | Excel Programming | |||
Return of position on page in excel | Excel Programming |