I wrote a macro to switch between 2 sheets, works well for my file.

I'd like to use it for any workbook and distribute it in the office. I
saved the code below as an addin and copy it to ...\Library, then chose
it on Tools\Add-ins, but nothing happens, can't find it anywhere.

I'd like to assign the macro to a shortcut or a button (ideally
ctrl+tab) so I can use it. Thanks.

'in Module1
Public OldSheetName As String

Sub ReturnToLastSheet()
Worksheets(OldSheetName).Activate
End Sub

'in ThisWorkbook
Private Sub Workbook_Open()
OldSheetName = ActiveSheet.Name
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
OldSheetName = Sh.Name
End Sub

***

Furthermore, if people like it, I may want to make it intall and assign
shortcut automatically, but have no idea how to do that yet, any hints?

I added more code for the addin, but still not working. Shortcut key
does not get assigned.

Here is what I have done so far. Thanks for help.

''''''A file named "GoBack.xla"
''''''In Class Modules::EventClass

Public WithEvents App As Application

''''''I put the following section in ThisWorkbook(using workbook
event), it doesn't work
Private Sub App_WorkbookAddinInstall(ByVal Wb As Workbook)
Application.MacroOptions Macro:="ReturnToLastSheet",
HasShortcutKey:=True, ShortcutKey:="Q"
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
OldSheetName = ActiveSheet.Name
End Sub

Private Sub App_SheetDeactivate(ByVal Sh As Object)
OldSheetName = Sh.Name
End Sub

'''''in ThisWorkbook

Dim AppClass As EventClass

Private Sub Workbook_Open()
Set AppClass = New EventClass
Set AppClass.App = Application
End Sub

'in ReturnToLastSheet

'might be wrong but it hasn't come to this far
Sub ReturnToLastSheet()
Worksheets(OldSheetName).Activate
End Sub

Here is a macro that does the job:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/29/2006 by Raymond Payette
'
' Keyboard Shortcut: Ctrl+t
'
If ActiveSheet.Name = "Sheet1" Then
Sheets("Sheet2").Select
Else
Sheets("Sheet1").Select
End If
End Sub

You cannot have Tab as a key, so I used T. This has to be set using
Record macro.

Save as type must be "Microsoft Office Excel Add-In" and it will make
an *.xla file that can then be distributed.


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=559448

Raypayette,

Have you tested this for any workbook, and how? I made something like
this but didn't work, so I built an addin that now works ok. If yours
works well can you send me a copy?

Besides, the macro seems only switch between two sheets that you
already know their names (though can be modified easily).

I realezed I can't use Tab. Thank you.

raypayette wrote:
Here is a macro that does the job:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/29/2006 by Raymond Payette
'
' Keyboard Shortcut: Ctrl+t
'
If ActiveSheet.Name = "Sheet1" Then
Sheets("Sheet2").Select
Else
Sheets("Sheet1").Select
End If
End Sub

You cannot have Tab as a key, so I used T. This has to be set using
Record macro.

Save as type must be "Microsoft Office Excel Add-In" and it will make
an *.xla file that can then be distributed.


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=559448