Solved

Excel VBA application stopped working

Posted on 2014-12-15
16
509 Views
Last Modified: 2014-12-16
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.
0
Comment
Question by:BrianSowter
  • 8
  • 5
  • 3
16 Comments
 

Author Comment

by:BrianSowter
Comment Utility
Since posting this I tried deleting all .exd files and rebooting.  The problem remains unchanged.
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
 

Author Comment

by:BrianSowter
Comment Utility
Hi Philip
Print 1+1 returns the answer 2
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
So, security is not the issue.

Do you have the same problem with a "Form Controls - Button" instead of an "ActiveX - Button"?
0
 

Author Comment

by:BrianSowter
Comment Utility
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
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
 

Author Comment

by:BrianSowter
Comment Utility
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
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:BrianSowter
Comment Utility
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
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:BrianSowter
Comment Utility
Phillip
Many thanks for your help
0
 
LVL 14

Expert Comment

by:Zack Barresse
Comment Utility
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
 

Author Comment

by:BrianSowter
Comment Utility
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
 
LVL 14

Expert Comment

by:Zack Barresse
Comment Utility
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
 

Author Comment

by:BrianSowter
Comment Utility
Thank you so much.  I ran the fixit and deleted the .exd files rebooted and now its ok,
0
 
LVL 14

Expert Comment

by:Zack Barresse
Comment Utility
Very welcome. :)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article, I will show you HOW TO: Create your first Windows Virtual Machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, the Windows OS we will install is Windows Server 2016.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
Viewers will learn how to apply various conditional formatting in Excel 2013.
Viewers will learn what comprises a theme in Excel 2013, as well as how to customize them.

772 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

14 Experts available now in Live!

Get 1:1 Help Now