Excel VBA application stopped working

I have a fairly large application using an Excel 2007 spreadsheet on Windows 7.
I have a number of Active X Control Command Buttons which run VBA code.  It has all worked for some time.
Today I find none of the command buttons work.
When I go into Design Mode I can highlight the buttons but when I click view code the VBA window comes up but at the top of the code not at the code for the button I have pressed.  Also when I RH click properties for the button I get the properties for the sheet no t for the button.  I tried making a new button and adding some code to it and it works fine.

i can run the code from the VBA window using F8.  The spreadsheet runs fine on my laptop and everything works.
I tried reloading Excel but no change.  It seems that the buttons are behaving like a graphic and are not triggering the code.
Brian SowterTechnical DirectorAsked:
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.

Brian SowterTechnical DirectorAuthor Commented:
Since posting this I tried deleting all .exd files and rebooting.  The problem remains unchanged.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It could be your security. Try this:

1. Open up your spreadsheet and Go into VBA mode.
2. Open the Immediate Window (Ctrl+G).
3. Type
print 1+1
and press Enter.
If you get the answer "2", it's not security. If you don't, it is.

Please let me know which it is.
0
Brian SowterTechnical DirectorAuthor Commented:
Hi Philip
Print 1+1 returns the answer 2
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
So, security is not the issue.

Do you have the same problem with a "Form Controls - Button" instead of an "ActiveX - Button"?
0
Brian SowterTechnical DirectorAuthor Commented:
Forms Control works fine.  I can also create a new Active X button which works.  But none of the existing Active X buttons work.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
My only suggestion is to convert the ActiveX buttons to the Forms buttons. I'm afraid I don't know why the ActiveX button suddenly doesn't work on one machine, but that's a way to code around the problem.
0
Brian SowterTechnical DirectorAuthor Commented:
Hi Phillip
I may have to do this.  Are you aware this is a known Microsoft update problem?  There is already quite a lot of traffic on the Experts Exchange site.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You mean this: http://www.infoworld.com/article/2858280/microsoft-windows/botch-brigade-kb-2553154-2726958-clobber-excel-activex-kb-3011970-silverlight-kb-3004394-root-cert.html

That doesn't look good. I wasn't aware of that - thanks for letting me know.

Hopefully Microsoft will fix it, but in the meantime, you've got the Forms version of the buttons to use for now.
0
Brian SowterTechnical DirectorAuthor Commented:
Phillip
Thanks for the link which has suitably scared other staff members in the company!

I am inclined to redo the Active X buttons.  The spin buttons are easier to implement because you don't need to write any code.  The other buttons can be implemented by a copy and paste followed by changing the button number in the code.

It is very peculiar that new Active X buttons are ok and the old ones not.

Do you think this is likely to be a permanent solution?  Or do you think it will get messed up again if there is another update?
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I think it was a goof by Microsoft in the first place - it shouldn't have happened, and it will probably be undone when they figure out a way to undo it.

But who knows when that will be.

Microsoft doesn't want to break your code in the first place; that makes a lot of unhappy customers. So hopefully they won't do it again - but there are no guarantees.

(Gulp.)
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
Brian SowterTechnical DirectorAuthor Commented:
Phillip
Many thanks for your help
0
Zack BarresseCEOCommented:
This is due to a security update by Microsoft rolled out earlier this month. Here is the blog post on it:

http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2014/12/13/forms-controls-stop-working-after-december-2014-updates-.aspx

Since this is a security update issue, Microsoft will most likely not take this update away. There is a FixIt you could also try: http://support.microsoft.com/kb/2703186/en-us

The solution is to close Excel, search your computer for *.exd files, delete them all, restart Excel.

HTH
0
Brian SowterTechnical DirectorAuthor Commented:
In My case deleting the .exd files did not work.

I need to change my programs to avoid using Active X.  Perhaps you can help me with this?

How do I convert the VBA code to a macro which I can start with a Forms button?   Do I simply create a blank macro and copy and paste the code in?  Or is there an easier way?  I have lots of code associated with Active X buttons.
0
Zack BarresseCEOCommented:
Did you try the FixIt as well? Make sure Excel is CLOSED when deleting the exd files. While it's not a surefire fix, it has helped predominantly everyone with this same issue (if it's the latest update that's affecting your controls).

ActiveX controls code is housed in the worksheet module, whereas Forms controls get standard sub routines associated with them, much like you would attach a macro to a shape. It really depends on your code, it's scope, and if it references the parent container or not. There could be no changes, there could be several, it depends on your code. Best way to find out is by posting your code and giving a description of what it should be doing. Sample files work best, of course.
0
Brian SowterTechnical DirectorAuthor Commented:
Thank you so much.  I ran the fixit and deleted the .exd files rebooted and now its ok,
0
Zack BarresseCEOCommented:
Very welcome. :)
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
Spreadsheets

From novice to tech pro — start learning today.