Title: Get the last deactivated worksheet
Tags: excel;vba;excel-formula;vba6
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.


Here is another answer: You firstly create a Public variable on top of ThisWorkbook code module (in the declarations area):
Public lastSheetName As String
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
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 &quot;You are coming from &quot; &amp; ThisWorkbook.lastSheetName
End Sub
Each time you activate another sheet, it will inform you from which sheet you come...
Comment for this answer: @Manideep Karthik: Didn't the above solution solve your problem? Did you find some time to test it?

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2021:00:04/26/21', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<excel><vba><excel-formula><vba6>', 'Title': 'Get the last deactivated worksheet', 'ViewCount': 46, 'id': 67266113, 'user_AboutMe': None, 'user_AccountId': 11897837.0, 'user_DisplayName': 'Manideep Karthik', 'user_Reputation': 89.0, 'user_Views': 81.0, 'user_id': 10538794.0}}