Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel VBA application stopped working

Posted on 2014-12-15
16
Medium Priority
?
542 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 3
16 Comments
 

Author Comment

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

Expert Comment

by:Phillip Burton
ID: 40502159
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
ID: 40502272
Hi Philip
Print 1+1 returns the answer 2
0
Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40502295
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
ID: 40502309
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
ID: 40502317
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
ID: 40502364
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
ID: 40502385
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
 

Author Comment

by:BrianSowter
ID: 40502485
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 2000 total points
ID: 40502488
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
ID: 40502514
Phillip
Many thanks for your help
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 40503279
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
ID: 40503454
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
ID: 40503480
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
ID: 40503621
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
ID: 40503734
Very welcome. :)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Viewers will learn what comprises a theme in Excel 2013, as well as how to customize them.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.

610 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