Store Previous active sheet as a variable

[nagovind]

Dear All,


Just trying to use a macro to go back to previous active sheet from the current active sheet


Is there is any method to store the value of previous sheet so as and when required while invoking a macro it should go to that sheet


Please advise


Thanks

 

[Luke M]

Put this in the ThisWorkbook module. Call the GoBack macro to go to previous sheet.

[pre]

'Define a local variable
Dim lastSheet As Worksheet

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
'Stored info for later use
Set lastSheet = Sh
End Sub

Sub GoBack()
'This is the macro to take us back to previous sheet
lastSheet.Activate
End Sub

[/pre]

 

[nagovind]

Thank You Luke


It is producing error saying that Object required


Run time error Error 424


Please advise

 

[nagovind]

Hi Luke


Code is working perfectly


But if the code is assigned to a short cut say ctrl L then it is producing error


If the shortcut is not made it is hard to call the function


Please advise

 

[nagovind]

Luke


It is perfect


Error is due to executing the command immediately after opening the document as there is no previous active sheets


THANK YOU

 

[Luke M]

Glad you were able to get it fixed. As an error check, we can change the last macro to this, if you want.

[pre]

Sub GoBack()
'This is the macro to take us back to previous sheet
If lastSheet Is Nothing Then Exit Sub 'Might want to add a msgBox...your call
lastSheet.Activate
End Sub

[/pre]