VBA for last active worksheet

[johannes2008]

Hello All

I am wondering if anyone knows the code that I can put in a button that will take me back to the last active worksheet, not necessarily the previous worksheet in the worksheet order. My sheets all have names with spaces.

Thanks
-Johannes

 

[Excelot]

Hi there. Sorry for adding to a topic that appears solved, but I had a similar problem with needing to jump back to an 'unknown' calling sheet. The easiest way I found to deal with it was to use Application.SendKeys. For example, I added the following macro to a button on the 'known' sheet...

<CODE>
' Use the ALT+Left Arrow combination to return to the previous sheet.
Sub JumpToCallingSheet_Click()
    Application.SendKeys Keys:="%{LEFT}"
End Sub
</CODE>

Of course, this assumes you haven't disabled that particular Excel hotkey. I know sendkeys is often avoided, but I've used this in Excel 2007/2010 successfully.

 

[Daxton A.]

I tried copying the code like it was written and it said "You have not switched sheets yet since opening the file!" as well....?

 

[Excelot]

Sorry for responding so late. I assume this was using the code I posted? If not, please ignore the rest of my reply . I wasn't able to reproduce the message you mentioned. I can't seem to attach an example file, so I'll describe how I set it up.

1. Open a new workbook with three sheets.
2. Create a shape on each sheet (to act like a button).
3. The shape on Sheet 1 should contain a hyperlink to jump to Sheet 3.
4. The shape on Sheet 2 should contain a hyperlink to jump to Sheet 3.
5. Add the code from above to a new module.
6. Assign a macro to the shape on Sheet 3. Assign the macro JumpToCallingSheet_Click.

Now, make sure Sheet 1 is activated.
Click the shape on Sheet 1 and it will jump you to Sheet 3.
Click the shape on Sheet 3, and you'll be taken back to Sheet 1.
Activate Sheet 2 and click the shape on Sheet 2 to be taken to Sheet 3.
Click the shape on Sheet 3 and this time you'll be taken to Sheet 2, the previously activated sheet.

I've used this where I have data for teams that drop down to (combined) individual data on another sheet. It's possible that the individual could have worked in more than one team, so not knowing which sheet to jump back to, this solution works perfectly for me. Of course, this assums that sheets have not been deleted in the meantime.

Hope this helps

 

[Gonzonator1982]

OK, I figured it out. It is actually the Workbook_SheetDeactivate event we want.

First, declare your Public Variable:

Public MyPrevSheet As String

Now, in the ThisWorkbook VBA module, create a Workbook_SheetDeactivate to capture your sheet name as you move off it like this:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    MyPrevSheet = Sh.Name
End Sub

Now, you can create a macro (in a Standard Module) that whenever run will go back to the previous sheet, like this:

Sub GoToPreviousSheet()
    
    If Len(MyPrevSheet) > 0 Then
        Sheets(MyPrevSheet).Activate
    Else
        MsgBox "You have not switched sheets yet since opening the file!"
    End If
    
End Sub

Just assign that code to a button, and that should do it!

I am trying to put this in my personal macro file so I can use it for all excel files. However, it just keeps giving me the message box, as if it is not recognising the deactivate event unless it occurs in the personal file itself.. can you help?

 

[brighamYoungMoney]

I'm having the same issue as Gonzonator1982 and Daxton and receiving the "You have not switched sheets yet since opening the file!" message from the latter portion of that IF statement. I think we are all referencing the code Joe posted and not Excelot. I'm wondering if it comes down to the placement of the Public variable? Do you place that in the standard module or the ThisWorkbook module?

 

[ZVI]

Put the below code into ThisWorkbook module of the PERSONAL workbook.
Save it (Ctrl-S) and reload Excel or just run macro Workbook_Open manually.
Use macro ThisWorkbook.GotoPrevious to activate the previous sheet/workbook.

' Put the below code into ThisWorkbook module of the PERSONAL workbook
Option Explicit
 
Private WithEvents App As Application
Dim objPrev As Object
 
' This runs automatically at opening
' You may Run it manually as well
Sub Workbook_Open()
  Set App = Application
End Sub
 
' Save previous sheet
Private Sub App_SheetDeactivate(ByVal Sh As Object)
  Set objPrev = Sh
End Sub
 
' Save previous workbook
Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
  Set objPrev = Wb
End Sub
 
' Assign this macro ThisWorkbook.GotoPrevious to any button
' Or press Alt-F8 and choose PERSONAL.XLSB!ThisWorkbook.GotoPrevious
Sub GotoPrevious()
  On Error Resume Next
  objPrev.Activate
End Sub

 

[ZVI]

Here is the improved version.
Macro is auto assigned to Alt-Left shortcut.
Warning appears if previous sheet has been hidden/deleted/closed.

' Put the below code into ThisWorkbook module
' Put the below code into ThisWorkbook module of the PERSONAL workbook
Option Explicit
 
Private WithEvents App As Application
Dim objPrev As Object
 
' This runs automatically at opening
' You may Run it manually as well
Sub Workbook_Open()
  Set App = Application
  Set objPrev = ActiveSheet
  Application.OnKey "%{LEFT}", Me.CodeName & ".GotoPrevious"
End Sub
 
' Save previous sheet
Private Sub App_SheetDeactivate(ByVal Sh As Object)
  Set objPrev = Sh
End Sub
 
' Save previous workbook
Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
  Set objPrev = Wb
End Sub
 
' Assign this macro ThisWorkbook.GotoPrevious to any button
' Or press Alt-Left to run it
Sub GotoPrevious()
  On Error Resume Next
  objPrev.Activate
  If Err Then
    Err.Clear
     MsgBox "You can not activate the previous sheet as it is deleted/hidden/closed"
  End If
End Sub

 

[SaraWitch]

Hello clever peeps.

I appreciate this thread is very old now, but I was just wondering why this code isn't working for me (Joe4, not ZVI as unfortunately this doesn't work at all for me and disables all the other macro buttons I have!). I have the codes in the correct place (at least I think I do! Where is the 'Public Variable' code put (I put it in 'ThisWorkbook' and then in every sheet)), assigned the macro to a button, switched sheets (both via macro buttons and tabs), but keep getting the message, "You have not switched sheets yet since opening the file!"

Any guidance would be gratefully received!

 

[gone2space]

I similarly am having trouble like @SaraWitch to get the examples from the previous page to work when trying to use VBA to move to the last active sheet

I've followed the instructions and have added the following to my "ThisWorkbook" sheet inside the VBA editor:

Public lastsheet As String

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
'    MsgBox Prompt:="You just left sheet:" & Sh.Name
    lastsheet = Sh.Name
End Sub

and have created a new module with the following text inside of it that is meant to make the workbook return to the previous sheet:

Sub ReturnPrevious(Control As IRibbonControl)
'On Error GoTo ErrHndl
   Sheets(lastsheet).Activate
'ErrHndl:
'    Exit Sub
End Sub

You'll see that I'm using this macro for a custom ribbon button and also I've commented out some lines to try and make debugging easier.

I've tested both sections using the debugger and both appear to be valid and work by themselves however, when the ReturnPrevious macro runs the "lastsheet" variable is suddenly set to blank and the macro errors out as it can't find the sheet.

Any advice on why the "lastsheet" variable is getting reset? Or perhaps I'm misunderstanding where things are going wrong?

 

[gone2space]

I similarly am having trouble like @SaraWitch to get the examples from the previous page to work when trying to use VBA to move to the last active sheet

I've followed the instructions and have added the following to my "ThisWorkbook" sheet inside the VBA editor:

Public lastsheet As String

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
'    MsgBox Prompt:="You just left sheet:" & Sh.Name
    lastsheet = Sh.Name
End Sub

and have created a new module with the following text inside of it that is meant to make the workbook return to the previous sheet:

Sub ReturnPrevious(Control As IRibbonControl)
'On Error GoTo ErrHndl
   Sheets(lastsheet).Activate
'ErrHndl:
'    Exit Sub
End Sub

You'll see that I'm using this macro for a custom ribbon button and also I've commented out some lines to try and make debugging easier.

I've tested both sections using the debugger and both appear to be valid and work by themselves however, when the ReturnPrevious macro runs the "lastsheet" variable is suddenly set to blank and the macro errors out as it can't find the sheet.

Any advice on why the "lastsheet" variable is getting reset? Or perhaps I'm misunderstanding where things are going wrong?

Realized the answer to my question after sleeping on it - I'd been declaring hte variable "lastsheet" twice, once in the "ThisWorkbook" section and also inside of the module. The double declaration caused the variable to lose it's assignment. Taking out the declaration in the "ThisWorkbook" fixed it!