Macro Stops Working.....

EE Pros,

I have a ActiveX Macro that just stopped working after working for months.  I have attached the WS and the control which now is simply a icon/box (when I copied it over).  The code is in the Worksheet that I have been using to expand and contract the outline.  That's all it does is expand and contract an outline.

Please take a look and let me know what has gone wrong.

Thank you in advance,

B.
Outline-Macro.xlsm
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Rory ArchibaldCommented:
You've almost certainly run into the activex bug that was caused by some MS updates. See my post here for information: http://excelmatters.com/2014/12/10/office-update-breaks-activex-controls/
0
James ElliottManaging DirectorCommented:
^

+1

Been causing me serious headaches since it happened !

Luckily quite easy to fix.
0
Bright01Author Commented:
Roy and James,

Thanks for the response.   Interesting that none of my other Active X Controls are failing........

So Roy, I read through the material; I'm not sure how to download or fix this issue; perhaps some specifics would be very helpful.  But before you do that, does the Control I sent work on your system in terms of collapsing and expanding the outline?  Also, is there a way to accomplish this without Active X?   My work around was to have three buttons and fire the macros..... It works but not nearly as "elegant" as having the single button that changes text as you fire each macro.

Thanks again in advance....... I look forward to your response(s)......

B
0
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Rory ArchibaldCommented:
1. It's Rory, not Roy. ;)

2. No your control doesn't work for me.

3. Yes you could do it with a Form button. I will amend and get back to you.
0
Rory ArchibaldCommented:
Here is a version with Form button instead.
Outline-Macro.xlsm
0

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
Martin LissOlder than dirtCommented:
After replacing the "icon/box" control with an ActiveX command button, I found that the problem is that the caption on the button says "Functional Areas" whereas your SumBtnCaps constant is "Questions,Functional_Areas,KPIs". Note that there's an underscore in the "Functional_Areas" part. If you remove that underscore it works.

I've attached a working workbook.
Q-28661127.xlsm
0
Bright01Author Commented:
Rory,  Thank you!  Your code works but will require some changes to my Master WB since you have the code in a module as well as the actual sheet.  I may use it...... Thank you very much!

Martin,

I can't get the Active Ex. Control to fire when I click on it.  It doesn't do anything.  I did have my Windows system perform an update yesterday and your first hunch may be right.  Any specific advice on how to enable ActiveX back to normal?

Much thanks also,

B.
0
Martin LissOlder than dirtCommented:
In my workbook go to the Visual Basic Environment and double-click on sheet29. Then find the CmdSummary_Click() code and select one of the lines in the sub. Then press F5. If the code runs then the problem is most likely the one Rory referred to because either pressing the button or running the code directly as I described, works for me.
0
Martin LissOlder than dirtCommented:
BTW the fix that Rory referred to basically boils down to deleteing the 2 or 3 files on your pc that have an exd (not exe!) extension.
0
Bright01Author Commented:
I get a error on this particular point:   cmdSummary

Variable not defined....

B.
0
Martin LissOlder than dirtCommented:
I get a error on this particular point:   cmdSummary
In my workbook?
0
Bright01Author Commented:
Martin,

I downloaded a copy of your Macro again.... I get an error and it asks if I want to recover the WB on the download.  Then it says, it's recovered.  


Next I open it and "Enable Macros".  Then I go to the button and nothing happens.  Here is the code that I end up with on my end (attached).

Thanks,

B.
Q-28661127-macro-no-fire.xlsm
0
Martin LissOlder than dirtCommented:
When I open the workbook you just attached, I get several errors that I believe are caused by my not being able to update the links in the workbook, but if I accept those errors I can eventually press the button and get the Functional Areas, KPIs and Questions reports. Once I close and reopen the workbook I only get one "error" which asks me if I want to update the links to which I respond "No" and everything works.

So here's what I think you should do. I assume that you have your original workbook. In that workbook remove the underscore from the SumBtnCaps constant. That was the only code change I made so see if it runs after you do that.
0
Bright01Author Commented:
This is really bazaar.  Your code works fine.  For me, If I try to replicate what you have done..... it doesn't work.  When I try to insert an Active X Control, it won't let me insert a control..... "Protect" is not on.  I noticed that in your code you had part of it in the Worksheet, and Part of it in a module.  I replicated that work exactly.  I even copied your control over to my WS.... but then it didn't recognize it as a Active X Control  This is the only WS in the entire WB where I have this problem.  Stumped.

b
0
Bright01Author Commented:
I also tried to remove the line in Functional_Areas to Functional Areas, and it still would not fire.

b.
0
Martin LissOlder than dirtCommented:
Copy your sheet to a new workbook, make sure there's no underscore in SumBtnCaps, add an ActiveX command button, name it CmdSummary and give it a try.
0
Martin LissOlder than dirtCommented:
I'm not sure which workbook you're talking about when you say "I noticed that in your code you had part of it in the Worksheet, and Part of it in a module" because there's no module in the workbook I uploaded.
0
Bright01Author Commented:
Martin,

Here's the problem.  I tried inserting a ActiveX control on both of my systems.  One system lets me do it; the other Excel instance comes back with, "Cannot insert object".  So it's something with the security or profile, in Excel, in the particular instance I am using.  Any ideas on how I can troubleshoot this (i.e. Options, Macro Enablement is all on.....)..............

Thank you,

B.
0
Martin LissOlder than dirtCommented:
Then use and accept Rory's solution in post ID: 40739934
0
Bright01Author Commented:
Rory and Martin,

Thank you for the time and effort in solving what is a very small problem but one that nags at you.  Rory, appreciate the code and will use yours since it works on my system and is simple.  Martin, thanks for the troubleshooting and in the end the advise to do what actually works!

All the best to both of you guys!

B.
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
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 Excel

From novice to tech pro — start learning today.