Excel VBA to go back to previous worksheet

In this article we use some VBA to remember which worksheet you have navigated away from, so that a ‘Go Back’ button can be placed to return to it.

 

We start with a menu page that has buttons navigating to our 4 worksheets.

 

BackToLastActiveSheet1

 

1) Press Alt+F11 to open your VBA window. From the Insert menu, choose Modules and paste the following, which means whenever GoBack is triggered it will look for the variable ‘PrevSheet’ and open that sheet:

 

 

BackToLastActiveSheet2

 

2) Double-click on ThisWorkbook in the Project Explorer panel on the left, then from the drop-down options at the top choose Workbook and then SheetDeactivate. Input PrevSheet = Sh.Name between the start and end lines of code. Also, make sure your routine is set to ‘Public Sub’ rather than ‘Private Sub’:

 

 

BackToLastActiveSheet6

 

Now simply create a¬†button with the text ‘Go Back’ and assign it the GoBack macro and it will return to the previous sheet whenever it is selected.

Leave a Reply

Your email address will not be published. Required fields are marked *