Solved

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

Posted on 2014-12-30
6
140 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
  • 3
  • 2
6 Comments
 
LVL 10

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 10

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

ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

Question has a verified solution.

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

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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 in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

821 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