use "Workbook_SheetDeactivate(ByVal Sh As Object)"

[elyanivson]

hi all,
I have tried to use the "Workbook_SheetDeactivate(ByVal Sh As Object)".
the reason I want to use it is to be able to switch from current sheet to the previous one.

so what I did was declaring "lastsheet" variable : Public LastSheet As Worksheet
then I used this in "ThisWorkbook" module :

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Set LastSheet = Sh
End Sub


the problem is that I don't know what to do next. every time I declaring a new subroutine and use Lastsheet.select then I get an error when calling this sun within a Macro.
so, what am I doing wrong? is there an easier way? or any other working way ?

thank you very much.

[Andy Pope]

Code in Thisworkbook module

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

    If g_objLastSheet Is Nothing Then Exit Sub

    Set g_objLastSheet = Sh
    
End Sub

Standard code module

Sub MoveBack()

Debug.Print "Before", ActiveSheet.Name, g_objLastSheet.Name
    g_objLastSheet.Activate
Debug.Print "After", ActiveSheet.Name, g_objLastSheet.Name
    
End Sub

No idea how you are using it though

[Andy Pope]

Code in Thisworkbook module

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

    If g_objLastSheet Is Nothing Then Exit Sub

    Set g_objLastSheet = Sh
    
End Sub

Standard code module

Sub MoveBack()

Debug.Print "Before", ActiveSheet.Name, g_objLastSheet.Name
    g_objLastSheet.Activate
Debug.Print "After", ActiveSheet.Name, g_objLastSheet.Name
    
End Sub

No idea how you are using it though

[elyanivson]

Code in Thisworkbook module

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

    If g_objLastSheet Is Nothing Then Exit Sub

    Set g_objLastSheet = Sh
    
End Sub

Standard code module

Sub MoveBack()

Debug.Print "Before", ActiveSheet.Name, g_objLastSheet.Name
    g_objLastSheet.Activate
Debug.Print "After", ActiveSheet.Name, g_objLastSheet.Name
    
End Sub

No idea how you are using it though

thank you.
when running the Moveback() , I'm getting a runtime error '424' object required..
any ideas?

[Andy Pope]

Duplicate post!

[Andy Pope]

Did the routine have anywhere to move back to? What did the debug message say was the Before sheet names.

Include the test for is nothing if you are trying to moveback without first having deactivated a sheet.