View Full Version : Revisit Last Accessed Cell
MachaMacha
10-16-2008, 06:14 AM
Hello I am using
Selection.ShowPrecedents
with a Ctrl keyboard shortcut to quickly go to a precedent cell. I would like to create another keyboard shortcut to go back to the previous cell.
For example If I clicked on Show Precedents in cell Sheet1!A1 and its precedent cell is Sheet2! B1 I would like to use a Ctrl keyboard shortcut in Sheet2!B1 that takes me back to Sheet! A1. The reason why I can't just trace dependents is because B1 may have many dependent cells and I just want to go back to A1.
Sorry this is jumbled.
Greetings MachaMacha,
It seems unclear to me at least, as to what you are trying to do. ShowPrecedents draws those cute little lines to precedent cells used in the cell's (being looked at) formula - but you mention wanting to "go back" to the cell. Are you looking to re-select the most recently selected cell (prior to current) or???
Bob Phillips
10-17-2008, 04:07 AM
Are you saying you want to go back to the first of the precedents, fisrt in the list order that is?
MachaMacha
10-17-2008, 05:35 AM
Hello, I am looking to re-select the most recently selected cell .
Bob Phillips
10-17-2008, 05:49 AM
That is a completely different requirement to the original one, so are you sure what you want?
Kenneth Hobs
10-17-2008, 06:59 AM
Insert a Module and paste:
Option Explicit
Public trackCount As Long
Public trackSheet()
Public trackCell()
Sub updateTrack(trackSheetName As String, trackCellName As String)
trackCount = trackCount + 1
ReDim Preserve trackSheet(trackCount)
ReDim Preserve trackCell(trackCount)
trackSheet(trackCount) = trackSheetName
trackCell(trackCount) = trackCellName
End Sub
Sub gotoNextToLastTrack()
Worksheets(trackSheet(trackCount - 1)).Range(trackCell(trackCount - 1)).Select
End Sub
In the saved xls, right click the excel icon to the left of the File menu, View Code and paste:
Private Sub Workbook_Open()
updateTrack ActiveSheet.Name, ActiveCell.Address
End Sub
For each sheet that you want to track, right click the sheet tab, View Code, and paste:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
updateTrack ActiveSheet.Name, Target.Address
End Sub
Play the Sub gotoNextToLastTrack, when you want to go to the previous cell. Of course multiple plays will seemingly toogle back to the initial cell. You can use a similar Sub to go back 2 levels or up to trackCell cells back. Only single cell selections are counted.
MachaMacha
10-21-2008, 02:57 PM
Thanks for the help Ken. I tried this but I get Run-time error '1004' Select Method of Range class failed
MachaMacha
10-21-2008, 03:00 PM
To be clearer with my problem lets say I have 3 different sheets, Sheet1, Sheet2, and Sheet3
Sheet1!B3=Sheet2!C6+Sheet3!D4
When I run
Sub TraceP()
Selection.ShowPrecedents
ActiveCell.NavigateArrow TowardPrecedent:=True, ArrowNumber:=1, LinkNumber _
:=1
Application.Run "BLPLinkReset"
End Sub
on Sheet1!B3, this sub takes me to the first precedent, Sheet2!C6. Now that I am at Sheet2!C6, I want to run a sub/keyboard shortcut that will take me back to Sheet1!B3.
Kenneth Hobs
10-21-2008, 05:06 PM
Then, you would play gotoNextToLastTrack.
You would need to post your xls for me to troubleshoot more I suspect.
MachaMacha
10-22-2008, 12:47 PM
Here is the file with the code already in the vba editor.
Ctrl + Q takes you to the first precedent. So when you do Ctra + Q on cell B3 in Sheet 1, it takes you to Sheet2 Cell C6.
My goal is to go back to Sheet 1 Cell B3 by playing the gotoNexttoLastTrack
Thanks
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.