Solved

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

Posted on 2014-12-30
6
129 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 80

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now