PDA

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.

GTO
10-17-2008, 03:54 AM
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