# Excel Macro - Previous Worksheet - Easy Question



## CWDENVER (Dec 1, 2006)

What is the code to send the user back to the previous worksheet? For example, the user is currently in sheet 1, he clicks a commandbutton which executes an if/then statement - if true, then he gets sent to sheet 2, if false, he is sent back to sheet 1. Thanks.


----------



## Zack Barresse (Jul 25, 2004)

Hello,

Could you be a little more specific? Are you referring to the index of the sheets (physical location)? Or are you talking about the last activated sheet?


----------



## CWDENVER (Dec 1, 2006)

Sorry, the last activated sheet. Basically, I've created an error message that pops up when the if/then statement is false, and I want the macro to reactivate the previously accessed sheet so the user can fix their error. Thanks.


----------



## Zack Barresse (Jul 25, 2004)

Setup a public variable in a standard module. Use a workbook event in your ThisWorkbook module for sheet deactivate. In that event, set the variable to the sheet name (the object is generally *Sh*). Then in your if/then statement, test if your public variable is null or not (assuming your variable is a String), then just activate that sheet name (i.e. Sheets(strYourVariable).Activate).

HTH


----------



## CWDENVER (Dec 1, 2006)

I'm sorry, I don't understand - I'm pretty new at this. Isn't there just a line of code that I can use that uses the previous method? Something like sheets.previous.select or something simple like that?


----------



## Zack Barresse (Jul 25, 2004)

No, there is no previous. Well, there is, but it doesn't do what you want. It acts on the previous sheet _physically_ in the list of sheets, not necessarily the last sheet the user was on. Try to follow what I described. Post back if you need help with it.


----------



## CWDENVER (Dec 1, 2006)

I have to apologize again, I haven't had any training in this kind of thing - and my experience is only about a month old. I've tried looking up all the terms you wrote about, but it becomes kind of a blur.


----------



## Zack Barresse (Jul 25, 2004)

Okay, not a problem. Here is what you need..

In any standard module, at the very top (below any *Option* statements you may have) put this ....


```
Public strLastSheet As String
```
I also had a routine to test this with in a standard module (or however you want to call this routine, so it could be in a worksheet module)...


```
Sub GotoLastSheet()
    On Error Resume Next
    ThisWorkbook.Sheets(strLastSheet).Activate
End Sub
```
The last part of this will be in the ThisWorkbook module of your workbook and uses a workbook event. Note there can only be one of each type of event, so if you already have this event, we'll need to accomodate (and you'll need to post your code)...


```
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    strLastSheet = Sh.Name
End Sub
```
That's it. This is all done in the Visual Basic Editor (VBE, press Alt + F11).

HTH


----------



## CWDENVER (Dec 1, 2006)

That worked perfectly. Thank you so much for your help!


----------



## Zack Barresse (Jul 25, 2004)

You're very welcome!

Don't forget to mark your thread Solved in the Thread Tools menu.


----------



## cavasta (Sep 30, 2007)

I've ressurected this posting because I want some advice on making a slight adaptation to the code supplied by firefytr.
I've copied firefytr's code into my workbook and it works fine on worksheets that aren't hidden. However, I've hidden some of the worksheets I use and have also written a code to hide these sheets once I've come out of them. If one of these hidden sheets is the last one I've been in and I want to return to it (by clicking a commandbutton that operates firefytr's code), how do I unhide it?
Thanks
cavasta


----------



## Zack Barresse (Jul 25, 2004)

cavasta, please start your own thread rather than hijacking somebody else's. You can paste a link to this thread in your new post if that would help. I'll be looking for it.


----------

