Solved

Personal.xls run a test on opening other sheets

Posted on 2013-06-12
8
271 Views
Last Modified: 2013-06-14
I want an ON_OPEN macro to run, not from code embedded in 'this workbook' but from personal.xls. Is this possible?
0
Comment
Question by:Grizzler
  • 5
  • 2
8 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39242194
Yes, but please be more specific.  Are you saying, "whenever I open *any* file in Excel, run a macro from my Personal.xls"?
0
 

Author Comment

by:Grizzler
ID: 39242220
When I open any workbook, I want a macro to fire. The sub will run tests against active workbook and prompt user accordingly. Want to trigger both from file open or double click to open from folder.
0
 

Author Comment

by:Grizzler
ID: 39242259
short answer "YES"
0
ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

 

Author Comment

by:Grizzler
ID: 39242270
current target excel2003
0
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 39246180
Hi,

To expand on matthewspatrick's response, save the attached Add-in file in an XLSTART folder.

BFN,

fp.
Q-28155488.xla
0
 

Author Closing Comment

by:Grizzler
ID: 39246217
Perfect! Thanks....
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39246258
You're welcome :)
0
 

Author Comment

by:Grizzler
ID: 39248237
So, if I might trouble you a bit further.... Looking for design strategy best practices....

In the past, I coded single purpose workbooks that contained code in embedded modules.
Works fine for "enter data, do stuff vba, output data", on locked sheet where the user doesn't alter the sheet structure.

Recently I started using Personal.XLS to add global functionality for Excel.

On another on going project, I have some legacy (system not by me) sheets that use internal and external links to get data and cross populate. The sheets are unlocked and edited freely by novices and people of some skill, alike. As you can imagine, the sheets are a mess. Too many issues to list....

One of my goals is to remove all the link formulas and replace them with a codebase that gets and places data and fixes errors. This is part of a project to migrate to an actual database soure, rather than Index/Match or Vlookup. The scale at which we have applied these methods is nearing critical mass and the fragile nature of what we are doing is becoming more and more evident to the people who can facilitate change.

I am going to remove as much code as possible from workbooks and make it built in....

Any way the simple generic question....

Advantages using XLA vs Personal.XLS?
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

810 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