Get the last deactivated worksheet

Question

Private Sub Worksheet_Deactivate()
Msgbox(worksheet.Name)
End Sub

How can I get the last deactivated Sheet once I press on any sheet other than the sheet of interest.

Answer 1

1. You firstly create a Public variable on top of ThisWorkbook code module (in the declarations area):

Public lastSheetName As String

2. Put the next code in the Workbook_SheetDeactivate event (also in ThisWorkbook code module):

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
   lastSheetName = Sh.name
End Sub

3. Then you can return the name of the last deactivated sheet with a simple Sub or inside another event code. Try pasting the next code in a standard module and run it. Of course, after you deactivated at least a sheet...

Sub LastDeactivatedSheet()
   MsgBox ThisWorkbook.lastSheetName
End Sub

3.a Or put the same code in the Workbook_SheetActivate event , in this way:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   MsgBox "You are coming from " & ThisWorkbook.lastSheetName
End Sub

Each time you activate another sheet, it will inform you from which sheet you come...