Solved

Excel VBA application stopped working

Posted on 2014-12-15
16
515 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
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
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
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 500 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
excel sheet how to remove filter 5 42
VBA Macro to print multiple active sheets 10 72
To array or not to array 11 55
help with an excel problem 10 29
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: 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…
Video by: Zack
Viewers will learn about using Excel in a browser with Excel Online.
Viewers will learn the basics of printing in Excel 2013 and how to adjust some common settings.

770 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