VBA Help: How to run macro in active workbook (via SheetActivate) with code only in personal.xls

[bama_rtr]

Hi all. I need some help. I have the following code below that allows me to switch back and forth between the active worksheet and the previous worksheet. However, to do this, the macros are stored in the ThisWorkbook module and a standard module of a specific file. What I want to do is store the macros in the personal.xls file so I it will work with any workbook I open.

Stored in ThisWorkbook:

Private Sub Workbook_Open()
mySheetName = Workbook.ActiveSheet.Name
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
OldSheetName = mySheetName
mySheetName = ActiveWorkbook.ActiveSheet.Name
End Sub

Stored in Module 1:

Public OldSheetName As String
Public mySheetName As String

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

I have copied this to the personal.xls workbook and messed around with it, but I think the problem is when I select a sheet on the active workbook (not personal.xls) it doesn't run the SheetActivate procedure which is located in ThisWorkbook of personal.xls as opposed to the ThisWorkbook in the active workbook.

When I run the ReturnToLastSheet macro, it allways results in an error 9 because OldSheetName= "" as opposed to an actual sheet name, I beleive, due to the reference of OldSheetName = mySheetName being in ThisWorkbook of personal.xls i.e. the variable (sheet name) is never stored.

The goal here is to not have the macros in personal.xls as to not copy the macros in to each and every workbook I work with. How can the macros be altered in personal.xls so that it works for any workbook, i.e. I can swith back and forth bewteen the current and previous sheet in the active workbook?? Please help.

 

[Richard Schollar]

Hi

You'll need to use Application level events for this:

In the ThisWorkbook module of Personal.xls in clude the following code (the Public Application variable needs to go at the top of the module in the declarations section ie before any subs):

Public WithEvents xlAPP As Application


Private Sub Workbook_Open()
Set xlAPP = Application
newSheet = ActiveSheet.Name
Application.OnKey "^E", "OldSheet_act"   '===will return to prior sheet on pressing Ctrl+Shift+E   ====modify as required
End Sub


Private Sub xlAPP_SheetActivate(ByVal Sh As Object)
oldsheet = newSheet
newSheet = Sh.Name
End Sub

In a standard module in Personal.xls include the following code:

Public oldsheet As String
Public newSheet As String
Sub oldsheet_act()
On Error Resume Next
Worksheets(oldsheet).Activate
End Sub

That's it! You'll needto save and reopen Excel to get it working.

 

[bama_rtr]

Thank you very much. Works great. A couple of follow up questions:

1) does application.OnKey always default to using the Shirft key? In your example of "^E" = Shift+Ctrl+E. Is there any way to make it just Ctrl+E, dropping the Shift?

2) I noticed that I needed to select, or activate, 2 worksheets before it would return to the previous sheet. I thought that when you activate the first sheet, it then sets the prior sheet to "oldsheet". It doesn't seem to do this until you do it twice. i.e. the first sheet select sets the "newsheet", then the second sheet select sets the "oldsheet" first then sets the "newsheet" to Sh.Name.

Doesn't matter I guess, just curious. Thanks, again.

 

[Richard Schollar]

1. Sure - just make in OnKey "^e","Oldsheet_act"

2. Yes I see what you mean - this is caused I believe by the Workbook_Open event firing before any other workbooks are opened, so the activesheet is actually on Personal.xls rather than the default workbook template that will open (typically Book1). Hence, I think you can get around this by changing the ThisWorkbook code to:

Public WithEvents xlAPP As Application


Private Sub Workbook_Open()
Set xlAPP = Application
Application.OnTime Now + TimeSerial(0, 0, 1), "apply_first_sheet"
End Sub


Private Sub xlAPP_SheetActivate(ByVal Sh As Object)
oldsheet = newSheet
newSheet = Sh.Name
End Sub

and the standard module code to:

Public oldsheet As String
Public newSheet As String
Sub oldsheet_act()
On Error Resume Next
Worksheets(oldsheet).Activate
End Sub
Sub apply_first_sheet()
newSheet = ActiveSheet.Name
Application.OnKey "^e", "OldSheet_act"
End Sub

which justs delays assigning a value to newSheet until the default workbook has loaded.

 

[bama_rtr]

Thanks for your help.