What was ActiveSheet before changed to new sheet?

[Tom L]

I've been searching for hours, can't find a solution but it's probably
simple...how can I tell what sheet was the ActiveSheet BEFORE they changed
to the current activesheet.

Example: User is on a particular sheet (let's say they're on "Main"), then
they click the tab to go to the "Prices" sheet. How can I tell what sheet
they're comming FROM - in this case the "Main" sheet? In the Workbook and
the Worksheet events Activate and Deactivate, I check the Sheet parameter
(SH) and it always shows "Prices", the sheet they're going too.

Thanks for your help!

 

[RB Smissaert]

Working OK here.
2 sheets, in the sheet1 code module:

Private Sub Worksheet_Activate()
MsgBox "activate", , "sheet1"
End Sub

Private Sub Worksheet_Deactivate()
MsgBox "de-activate", , "sheet1"
End Sub

in the sheet2 code module:

Private Sub Worksheet_Activate()
MsgBox "activate", , "sheet2"
End Sub

Private Sub Worksheet_Deactivate()
MsgBox "de-activate", , "sheet2"
End Sub

First I get the Deactivate from the sheet I came from, then the Activate
event
from the sheet I went to. Try for yourself.


RBS

 

[Guest]

Tom, this code indicates one approach. Put it in your ThisWorkbook module.

Regards,
Bill


Private g_strLastSheetName As String

Private Sub Workbook_Open()
g_strLastSheetName = ThisWorkbook.ActiveSheet.Name
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Call MsgBox("last sheet: " & g_strLastSheetName)
g_strLastSheetName = ThisWorkbook.ActiveSheet.Name
End Sub

 

[Peter T]

You could try this in the thisworkbook module -

Dim mSh As Object
Dim mShtName As String

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim sName As String
Dim sMsg As String

On Error Resume Next
sName = mSh.Name
On Error GoTo 0

If sName = "" Then
sMsg = mShtName & " has just been deleted"
Else
sMsg = mShtName & " deactivated"
End If
MsgBox sMsg, , Sh.Name & " activated"

End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Set mSh = Sh
mShtName = Sh.Name
End Sub

Regards,
Peter T

 

[Joergen Bondesen]

Hi Tom

Main sheet: right clik on tab - View Code and place below.

Take a close look at the 2 arrows. Left: Object and right: Procedure.


Option Explicit

Private Sub Worksheet_Deactivate()
MsgBox Me.Name
End Sub


Enjoy

 

[Tom L]

Thanks everyone for your code examples, it gave me the solution I needed!!

Tom