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

B

bama_rtr

New Member
Joined
Mar 26, 2009
Messages
8
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
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):

Code: 
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:

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.
 
Upvote 0
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.
 
Upvote 0
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:

Code: 
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:

Code: 
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Running a macro in workbook now running some of the macro in the personal.xlsb file!
Replies
7
Views
169
Sallyprit
S
L
  • Solved
Fixing VBA code
Replies
3
Views
475
Joe4
Joe4
E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
265
offthelip
O
M
  • Solved
AutoSave on exit stopped working
Replies
2
Views
508
Mech.Mon
M
J
  • Question
Issues combining macros - how can I get a formula macro to run???
Replies
1
Views
399
Micron
M

Forum statistics

Threads
1,214,376
Messages
6,119,174
Members
448,870
Latest member
max_pedreira

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top