Solved

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

Posted on 2014-12-30
6
150 Views
Last Modified: 2015-01-02
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
Comment
Question by:SuraDalbin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 11

Expert Comment

by:Ray
ID: 40524135
After an admittedly hurried glance . . .
Sounds to me like the "newsheet" has not yet been declared in this instance
0
 

Author Comment

by:SuraDalbin
ID: 40524178
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
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 40524948
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
Independent Software Vendors: 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!

 
LVL 11

Expert Comment

by:Ray
ID: 40528379
Excellent work Brad, as always!  

Sorry for my lack of further response Sura.  Holidays had me preoccupied.  Please ensure Brad gets full credit :-)
0
 

Author Closing Comment

by:SuraDalbin
ID: 40528761
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
 

Author Comment

by:SuraDalbin
ID: 40528811
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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question