MS Excel 2013, Where can I save a vb code so that it is avaiable every time I open and excel file

I have a VB scriprt that I want to load everytime I open an Excel spreadsheet. It should load up if it is an existing spreadsheet or a brand new spreadsheet.

Questions: How do I make this happen? Where do I need to save the VB Script so that it opens in any excel spreadsheet or format that I open?  

Thanks in advance for your assistance.

Ldufresne
LVL 1
Lou DufresneIT Business Analyst CSM / Project ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RayData AnalystCommented:
You can store them in your "personal workbook".  This is a file that opens every time Excel loads.

No need to 're-invent the wheel', so take a look at the microsoft page below for more information and post back if you have any further questions.

https://support.office.com/en-ca/article/Copy-your-macros-to-a-Personal-Macro-Workbook-aa439b90-f836-4381-97f0-6e4c3f5ee566
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
grendel777Commented:
And if you want the code to RUN when you open the workbook, check out the Workbook_Open event.
https://msdn.microsoft.com/en-us/library/office/ff196215.aspx
0
Roy CoxGroup Finance ManagerCommented:
I prefer saving as an Excel addin because it easier to move to different computers or distribute to friends and colleagues. Excel addins are available in the background whenever Excel is open.

Excel addins
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Lou DufresneIT Business Analyst CSM / Project ManagerAuthor Commented:
information:

Ray
I tried Ray's method and I had some issues with making it work. I will try again later today.

Grendel77
I mislead Grendel77 in that I am trying to have this code avail on any open spreadsheet but I do not want it to run when the spreadsheet first opens.

Roy Cox
I have tried to do this as an add-in but I need to understand how to use addins and how to put code in the add in spreadsheet. I there any other articles that you can recommend?

I will continue to work on this after I hear back from any of you.

Thanks for you patience
Ldufresne
0
RayData AnalystCommented:
Ldufresne,
What issues did you have in adding a macro to the personal workbook?  What, specifically, did you need help with?
0
Roy CoxGroup Finance ManagerCommented:
Here's some more links

Create Excel Addin

Creating Excel Addins

Video

Post back with specific questions
0
Lou DufresneIT Business Analyst CSM / Project ManagerAuthor Commented:
Ray,
   Below is the code I am trying to place in the Personal Macro Workbook as you have suggested. I followed the directions and it does not work the way I expected.  It may be the code that is the problem. It works well as is if you place as .

Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)

    Static rngOld As Range
    On Error Resume Next
    Target.Interior.ColorIndex = 6
    rngOld.Interior.ColorIndex = xlColorIndexNone
    Set rngOld = Target

End Sub
0
Roy CoxGroup Finance ManagerCommented:
You cannot put event code into an addin or Personal workbook. You can actually write acode that will trigger this. Is that all the code?

Which version of Excel are you using?
0
Roy CoxGroup Finance ManagerCommented:
Here's an example that runs your code in any workbook.

Please note it is only an example and needs to be saved as an addin as explained in the articles I posted links to.

To test it just open the example workbook as usual and select cells, the code will run. Open another workbook whilst the test one is open and select cells, the code will run in the newly opened workbook.

I've actually adapted this from an addin that I wrote that has a very similar function,  but will actually work on cells that are already coloured.

If this does what you want I can convert to an addin for you.
Addin-Example.xlsm
0
Lou DufresneIT Business Analyst CSM / Project ManagerAuthor Commented:
Roy Cox
Thi is all the code that I am trying to runm as listed below. I am using Microsoft Excel 2013

Code:

Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)

     Static rngOld As Range
     On Error Resume Next
     Target.Interior.ColorIndex = 6
     rngOld.Interior.ColorIndex = xlColorIndexNone
     Set rngOld = Target

 End Sub

Thanks for your help
Ldufresne19
0
Roy CoxGroup Finance ManagerCommented:
Have you tried the workbook that I uploaded?
0
RayData AnalystCommented:
As Roy is saying, any 'event' based macros will not function via the personal workbook.  My, bad, assumption was that you were executing something by calling the macro not on an event.
0
Roy CoxGroup Finance ManagerCommented:
Ray

No-one would know that without seeing the code that's why I like to see an example workbook. The code in the workbook that I posted runs in any workbook and just needs saving as an addin
0
Lou DufresneIT Business Analyst CSM / Project ManagerAuthor Commented:
Roy,
    Thanks everyone for being patient with me . I was not able to get to a PC for about 3  days but now I am back and ready to go.
     
Yes, I have just tried the spreadswheet that you attached. It is exactly what I was trying to do.

What would I need to do to convert this to an add-in?

Ldufresne
0
Roy CoxGroup Finance ManagerCommented:
If you only want it on your PC simply save as and choose  .xlam. Make a note of where it's saving to in case you need to access it. It will work in every workbook that you open. If you need to switch it on and off I would need to write some more code and add a button to the Ribbon
0
Lou DufresneIT Business Analyst CSM / Project ManagerAuthor Commented:
Thank everyone. I learned a lot from the answers to this question.

Ldufrresne
0
Roy CoxGroup Finance ManagerCommented:
Glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.