last active sheet

dcompagnone

Board Regular
Joined
Dec 14, 2007
Messages
119
Hi,

Anyone help me with a small piece of code that will allow me to add a 'retur to last active sheet' type functionality to excel?

For example:

I have sheets 1 - 3 that contains all my data. from each of those, there is a link to a document on sheet 4 which you can populte, print and email. What I want to be able to do is then have a button that allows the user to go back to the sheet they came from rather than having to search through tabs for it.


This is a simplified version of my doc. I actually have nearly 100 sheets of data all linking to my 'printable/emailable' sheet.

Help appreciated.

Dom
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
just an idea out there.

You could declare a public variable in VBA (Dim prevWorksheet As Worksheet)
and set it as the worksheet with the event worksheet_Select

Then in your master sheet, you can activate it like (prevWorksheet.Activate)
and it should automatically bring the user back to his/her previous worksheet.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Dom

You could do this with code - insert a standard module in your workbook (via the VBE) and place the following code inside it:

Code:
Public shtNm As String
Sub select_shtNm()
On Error Resume Next
Sheets(shtNm).Select
End Sub

Then place the following in the ThisWorkbook module of the workbook:

Code:
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
shtNm = Sh.Name
End Sub

And then you just need to assign the macro select_shtNm to a button or to a shortcut key.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,843
Messages
5,525,177
Members
409,629
Latest member
McGuilliam

This Week's Hot Topics

Top