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

 

[Joe4]

When you say "last" sheet, do you mean:
- the previous worksheet you were working on
or
- the right-most worksheet (the very last sheet in your workbook)?

The second one is pretty easy, while the first one could be a bit tricky. You would need some sort of code to track yourself moving around the workbook.

 

[johannes2008]

Hey Joe

The first one, the last sheet I was on. I was thinking that the best way would be to create a global variable that every time you switched to a new sheet it changed that variable. Then used that variable to determine which sheet to go to when you click the "Back" button.

The part I don't know is how what kind of variable and how to change its value. If I can make it so that the only way to change sheets is by clicking buttons I suppose then I could write the code that changes the variable in the code for button. But I don't know how to make it so that people cant use the tabs to navigate sheets without making them "Very hidden" which then makes my code not work as I am always referencing cells from other sheets.

Any thoughts?
-Johannes

 

[Joe4]

I was thinking that the best way would be to create a global variable that every time you switched to a new sheet it changed that variable. Then used that variable to determine which sheet to go to when you click the "Back" button.

Yes, I had the exact same thoughts. That is how I would try to approach it, in theory, but coming up with code to do that is another story. Off the top of my head, I am not sure how to do that and would have to play around with some things.

I think that some Event Procedure VBA code is probably going to be needed here. The Workbook_SheetChange event procedure looks promising. Here are a few links on it:
http://www.cpearson.com/excel/Events.aspx
http://support.microsoft.com/kb/213523

 

[Joe4]

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!

 

[Norie]

Johannes

Do you have code that is changing the active sheet and that's causing problems?

 

[johannes2008]

Joe!

It freakin works! Thanks for the help I can honestly say I never would have figured that out.

Thanks again
-Johannes


Norie

I am also trying to determine how to disable the tabs, so that the only way to change sheets is with buttons. I tried using the "veryhidden" property but then my vba gave me errors and said that there was no sheet with that name.

If you have any suggestions on disabling the tabs I would be glad to hear them.

Thanks
-Johannes

 

[ZVI]

...how to disable the tabs, so that the only way to change sheets is with buttons.

Try:

Sub HideTabs()
  With ActiveWindow
    .DisplayWorkbookTabs = False
    .TabRatio = 0
  End With
End Sub

 

[Weaver]

Just an observation, but what happens if the previously active sheet has been deleted or renamed in the meantime?

Suggested mod:

Sub GoToPreviousSheet()
    on error goto noSheet
    If Len(MyPrevSheet) > 0 Then
        Sheets(MyPrevSheet).Activate
    Else
        MsgBox "You have not switched sheets yet since opening the file!"
    End If
    exit sub
noSheet:
    MsgBox "Sheet '" & myPrevSheet & "' can no longer be found"
    myPrevSheet = ""
End Sub

 

[btbuff]

This works great for me when it is in an excel macro enabled workbook. However, when I save it as an add-in and activate it in excel it gives me the "You have not switched sheets yet since opening the file!", indicating the variable isn't being updated/ or the sheetDeactivate doesn't work. Any suggestions?