Need code that returns to last active sheet in Excel 1

[Acquaman]

Hello,
Thank you for your response, I really appreciate it.

I am planning a separate sheet as a help screen for the Excel App. I am making but, it is important that I return the user to the same sheet they were on last when they leave it. Any way to do this with code? I will be greatfull for any help you can give me. Thanks.

Sincerely,
Acquaman

 

[SkipVought]

Hi,

Use the Worksheet_SelectionChange event to store the last selected address

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Sheets("SomeOtherSheet").[A1] = Target.Address
End Sub

Then when you want to return use that address...

Sheets("TheReturnSheet").Activate
Range(Sheets("SomeOtherSheet").[A1]).Select

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Acquaman]

Skip,
Thanks SO much! Off to try it...
Acquaman

 

[Acquaman]

Dear Skip,
I was unsuccessful in implementing your code into my application. It is probably just me but, if you have a moment I would like to clarify my question.

Say there are 3 worksheets in a workbook. The sheets are named A, B, & C. If I locate the help screen on the sheet named C, will your code return the user to either sheet A or sheet B (the sheet they were working on)without knowing in advance which sheet they were working on when they called for screen C? If the answer is yes, could you please provide any additional information that might help me understand how to institute your code. Thanks, so much.
Sincerely, Acquaman

 

[PHV]

Example from Help file:
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
MsgBox Sh.Name
End Sub
So, the idea is to maintain a Global variable containing the last Worksheet object activated when the user activate your Help sheet.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

So you have 3 sheets.

In the Workbook_SelectionChange & Workbook_SheetActivate events (instead of the Worksheet_SelectionChange event) store the Sh.Name

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name = "MyHelpScreen" Then Exit Sub 'or whatever that sheet is names
    [Prev] = [This]
    [This] = Sh.Name
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "MyHelpScreen" Then Exit Sub 'or whatever that sheet is names
    [Prev] = [This]
    [This] = Sh.Name
End Sub
Sub ReturnToSheet()
    Sheets([Prev].Value).Activate
End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Acquaman]

Thank you both. I think that you have given me the answer. I will try it in the A.M. and let you know.
Regards, Acquaman

 

[Acquaman]

Well, Here is what finally worked for me. Thank you both so much for your help. I couldn't have done it with out you!

'For this example the sheets in the workbook are named Sheet 1, Sheet 2, and Sheet 3.
'Sheet 3 is the sheet all sheets will select and the sheet from which we will return
'to the sheet last working on.

'In the THIS WORKWOOK object goes the following routine -
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Sheet3" Then Exit Sub
Range("A1").Value = Sh.Name 'Places the name on the tab in the worksheet in cell A1.
End Sub

'In the first "SHEET 1" object goes the following (Repeat in each SHEET object
'you wish to return to)-
Private Sub CommandButton1_Click()
Range("A1").Copy
Worksheets("Sheet3").Activate
Application.Goto Reference:="Name3" 'COPIES TO A CELL NAMED "NAME3"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

'In the SHEET object that you wish to return from (Sheet 3 in this case) goes the following -
Private Sub CommandButton1_Click()
Range("A1").Select
Worksheets(Range("A1").Value).Activate
End Sub

Thanks again. Your help was invaluable.
Sincerely,
Acquaman

 

[SkipVought]

Acquaman,

Tek-Tips Rocks!

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}