Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for returning to the previously selected cell
Hello - I am trying to figure out how to create a macro that will
allow me to jump to my prior location in a workbook. For example, I will be in Sheet1:A35, then I will need to go to Sheet5:D73. What I am trying to do is come up with a shortcut to immediately jump back to Sheet1:A35. Does anyone have some code put together that does this? Thanks for any suggestions! - F |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for returning to the previously selected cell
Not real sure if this will do what you want, but because Excel does not have
a "BeforeChange" event it can be somewhat complicated to do what you are wanting to do. Put the following inside "ThisWorkbook" and watch what happens as you move from sheet to sheet with the Message Box prompts. Maybe something here will work or maybe you can figure out what you need from here. Sorry not to be of much help on this topic... Mark Ivey '**** Code Starts Here **** Private wbOpenRange As Variant Private shActivateRange As Variant Private selChgRange As Variant Private Sub Workbook_Open() wbOpenRange = ActiveSheet.Name & "!" & ActiveCell.Address test End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) shActivateRange = Sh.Name & "!" & ActiveCell.Address test End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) selChgRange = Sh.Name & "!" & ActiveCell.Address test End Sub Sub test() If wbOpenRange = "" Then wbOpenRange = "Nothing" End If If shActivateRange = "" Then shActivateRange = "Nothing" End If If selChgRange = "" Then selChgRange = "Nothing" End If MsgBox "ON OPEN EVENT: " & wbOpenRange & vbCrLf & vbCrLf _ & "SHEETACTIVEATE EVENT: " & shActivateRange & vbCrLf & vbCrLf _ & "SELECTIONCHANGE EVENT: " & selChgRange End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for returning to the previously selected cell
I'm kind of using Mark's idea here, but how about:
In ThisWorkbook: Option Explicit Dim gNewTarget As Range, gOldTarget As Range Private Sub Workbook_SheetSelectionChange( _ ByVal Sh As Object, _ ByVal Target As Excel.Range) Set gOldTarget = gNewTarget Set gNewTarget = Target End Sub Public Sub Goback() Sheets(gOldTarget.Parent.Name).Activate gOldTarget.Select End Sub Now you still have to connect the shortcut to the Sub Goback. Dave D-C Mike C wrote: Hello - I am trying to figure out how to create a macro that will allow me to jump to my prior location in a workbook. For example, I will be in Sheet1:A35, then I will need to go to Sheet5:D73. What I am trying to do is come up with a shortcut to immediately jump back to Sheet1:A35. Does anyone have some code put together that does this? Thanks for any suggestions! - F |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for returning to the previously selected cell
Give the following a try. Add a Module to your project and copy/paste these
declarations into its code window... Public OldCell As Range Public OldSheet As Object Next, double click the ThisWorkbook entry in the Project list and copy/paste this code into its code window.... Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) Set OldCell = Target Set OldSheet = Sh End Sub Finally, use the following in your own subroutine or function... Sub YourSubroutine() Dim SavedOldSheet As Object Dim SavedOldCell As Range Set SavedOldCell = OldCell Set SavedOldSheet = OldSheet ' ' <<< Place your own code here ' SavedOldSheet.Activate SavedOldSheet.Range(SavedOldCell.Address).Select End Sub Rick "Mike C" wrote in message ... Hello - I am trying to figure out how to create a macro that will allow me to jump to my prior location in a workbook. For example, I will be in Sheet1:A35, then I will need to go to Sheet5:D73. What I am trying to do is come up with a shortcut to immediately jump back to Sheet1:A35. Does anyone have some code put together that does this? Thanks for any suggestions! - F |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with sebastienm's code - Returning To Previously Selected Worksheet | Excel Programming | |||
Macro to concatenate previously selected cells | Excel Programming | |||
Clearing what you have previously selected | Excel Programming | |||
referencing previously selected cells after macro execution begins | Excel Programming | |||
Selection the previously selected cell | Excel Programming |