[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • Last Modified:

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

Hello Experts,

I've been using the code I found at www.mrexcel.com to navigate back and forth between worksheets in a workbook.  I followed the instructions, as to saving the two sets of code in the "ThisWorkbooK" and the second part in a regular module, both codes are saved in an Add-In file. I am using Excel 2010.

The codes are as follows:
ThisWorkbook:
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

Open in new window


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

Open in new window


The code works correctly if Excel is first opened and any subsequent files are opened using the Open File function of Excel. However, the problem that I'm having is that if I open an Excel file from Windows Explorer, I get Run-time error '91': Object variable or with block not set.  When I click Debug, it takes me to Module 1, specifically to this sub:

Sub apply_first_sheet()
newSheet = ActiveSheet.Name
Application.OnKey "^e", "OldSheet_act"
End Sub

Open in new window


With this part of the code highlighted newSheet = ActiveSheet.Name.

Any help on this will be greatly appreciated.

Thank you
0
SuraDalbin
Asked:
SuraDalbin
  • 3
  • 2
1 Solution
 
RayData AnalystCommented:
After an admittedly hurried glance . . .
Sounds to me like the "newsheet" has not yet been declared in this instance
0
 
SuraDalbinAccountantAuthor Commented:
Hello Ray,

Thank you for your response.  Would you be able to help me change the code so it works correctly.  I understand what each parts of the code do at the different levels, but my knowledge of VBA is not that extensive.  The way I understand it is, if the "newsheet" has not been declared in this instance (module), I'm tempted to say:

Sub apply_first_sheet()
If ActiveSheet.Name = "" Then
    GoTo ErrHandler
Else
newSheet = ActiveSheet.Name
End If
Application.OnKey "^e", "OldSheet_act"
ErrHandler:
End Sub

Open in new window


I tested this, but I still get an error message :(
0
 
byundtCommented:
It is good practice to check the box in the VBA Editor...Tools...Options...Editor menu item for "Require Variable Declaration". Doing so puts "Option Explicit" at the top of every module sheet you create. I do it to catch typos when the variable spelling in code I type doesn't match the Dim statements.

Because I do this, I had to add a variable declaration for xlApp in your ThisWorkbook code:
Option Explicit

Private Sub Workbook_Open()
Dim xlApp As Object
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

Open in new window


To avoid your actual error, I added an error handler section to sub apply_first_sheet that called itself using Application.OnTime in the event of an error.
Option Explicit

Public oldsheet As String
Public newSheet As String

Sub oldsheet_act()
On Error Resume Next
Worksheets(oldsheet).Activate
End Sub

Sub apply_first_sheet()
On Error GoTo Errhandler
newSheet = ActiveSheet.Name
Application.OnKey "^e", "OldSheet_act"
Exit Sub

Errhandler:
    Application.OnTime Now + TimeSerial(0, 0, 1), "apply_first_sheet"
End Sub

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
RayData AnalystCommented:
Excellent work Brad, as always!  

Sorry for my lack of further response Sura.  Holidays had me preoccupied.  Please ensure Brad gets full credit :-)
0
 
SuraDalbinAccountantAuthor Commented:
Thank you byundt, i tweaked the code a bit because the code didn't work correctly without the public variable.  The error handler that you suggested does the trick. Thank you very much.
0
 
SuraDalbinAccountantAuthor Commented:
Thank you for following up, Ray!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now