What was ActiveSheet before changed to new sheet?
 

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!

What was ActiveSheet before changed to new sheet?
 

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


"Tom L" wrote in message
...
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!

What was ActiveSheet before changed to new sheet?
 

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




"Tom L" wrote:

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!

What was ActiveSheet before changed to new sheet?
 

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

"Tom L" wrote in message
...
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!

What was ActiveSheet before changed to new sheet?
 

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
--
Best Regards
Joergen Bondesen


"Tom L" wrote in message
...
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!

What was ActiveSheet before changed to new sheet?
 

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

Tom


"Tom L" wrote in message
...
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!