Back to Previous Sheet (Leo)

[Guest]

Hi,
In an individual large workbook, I put these codes in ThisWorkbook module to
get back previous sheet I navigate;

Public CurrentSheet As Worksheet

Private Sub Workbook_Open()
Application.OnKey "^+B", "thisworkbook.GoBack"
End Sub

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

Public Sub GoBack()
If Not CurrentSheet Is Nothing Then CurrentSheet.Activate
End Sub

so when I move to another sheet in a large workbook containing many sheets,
I can go back to previous one just by pressing Ctrl+Shif+B.
This works fine for an individual workbook, but when I tried to copy the
same on my AddIn to use it for all open or new workbooks, it failed, and the
code did nothing. It seems that CurrentSheet Variable is always Nothing.
I appreciate if you would tell me what is wrong, and how can I fix it??!

 

[JW]

If you have that code in the ThisWorkbook module of your addin, then
it will only work in that addin. Sounds to me like this might call
for a class module at the application level.
Chip has a nice page dedicated to class modules. Have a look.
http://cpearson.com/excel/Classes.aspx