asked on

Excel 2010 ActiveX button controls stop working after vba code macro executes

I have a spreadsheet that has button controls which execute some vba code/macros. It works using Excel 2007 but for some reason it does not work correctly in Excel 2010. There are actually three buttons in particular and I have narrowed the problem down to a line of code:

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

After this line of code runs the button controls on the spreadsheet are disabled. I do not believe this is related to any windows/office updates as I have tested it before and after applying service packs/updates. The file is saved with a .xlsm extension. I haven't found any other issues running the other vba code included in the spreadsheet, just this one line.

Roy Cox

That code should not cause any version issues.

Can you attach an example file?

schwientekd

ASKER

Sorry I cannot post the file.

Roy Cox

What is the full code in one of the problem buttons?

schwientekd

ASKER

Private Sub CommandButton2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

With ActiveSheet.Shapes("CommandButton2")
.Left = [j7].Left
.Top = [j7].Top
End With

Application.Run "Print_Report"

Worksheets("Assumptions").Activate
Range("G5").Select
Application.CutCopyMode = False

End Sub

Sub Print_Report()

Sheets(Array("Front Cover", "Explanation", "Plan Results", "Assumptions", "Plan Summary", "Plan Detail", "Definitions")).Select

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub

Roy Cox

Have you tried removing the code in the button and just running a ne line of code to see if the button still works, something like

MsgBox "Hello World"

Open in new window

I can't see why you are using Application.Run.

Try

Option Explicit

Private Sub CommandButton2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

 With ActiveSheet.Shapes("CommandButton2")
 .Left = [j7].Left
 .Top = [j7].Top
 End With

 Print_Report

     Worksheets("Assumptions").Activate
     Range("G5").Select
     Application.CutCopyMode = False

 End Sub


 Sub Print_Report()

     Sheets(Array("Front Cover", "Explanation", "Plan Results", "Assumptions", "Plan Summary", "Plan Detail", "Definitions")).Select

 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

 End Sub

Open in new window

schwientekd

ASKER

Using code in the button works fine.

I tried using the 2nd batch of code and it produces the same result. After running the code the buttons still do not work and going into design mode does not let me edit the buttons, same as before.

Roy Cox

The best thing is to use Forms buttons, they are less problematic.

Have you checked the Trust Centre settings.

File -> Options -> Trust Center - Trust center Settings
make sure that "Disable all controls without notification" is unchecked

schwientekd

ASKER

I was on vacation and didn't have time to get back to this until now.

The trust center settings were set correctly as advised above.

I moved the code over to form buttons instead of ActiveX controls. Now when I run the code the buttons shrink (I can get them to appear back by clicking on the tiny little button they become). After I click on the button I get the following message and nothing will run.

RUn-time error '-2147024891 (80070005)':

Access denied. You do not have enough privileges to complete this operation.

Continue | End | Debug | Help (can only click on End and Help buttons)

Roy Cox

Check the AutoSize property of the button. If it's set to False try changing it to True.

I think this is actually a problem with ActiveX controls though.

schwientekd

ASKER

Nothing seems to work. Not sure what else to try.

Roy Cox

I've found this that might help

Resizing Buttons Quirk

schwientekd

ASKER

Thanks for the link, I will check this out. The active x buttons don't have the resize problem but the form buttons you suggested trying out do. My real problem isn't the buttons but how the controls all lock after running the code and getting the Access Denied message when using the form buttons.

Roy Cox

It really doesn't make sense, I can't think of anything else, but I'll keep looking.

Roy Cox

I really think this is some sort of bug with controls,. Have you considered not bothering with buttons but creating your own customised tab in the Ribbon?

schwientekd

ASKER

I did not consider that but sounds like a good thing to try!

I see how I can add a macro the custom tab but how do I add VBA code that calls a macro? Here is the VBA code I want to add:

With ActiveSheet.Shapes("CommandButton1")
.Left = [j4].Left
.Top = [j4].Top
End With

If Range("hceflag").Value = "No" Then

Application.Run "TSC_Hildi_NoHCE"

ElseIf Range("nhceflag").Value = "No" Then

Application.Run "TSC_Hildi_NoNHCE"

Else

Application.Run "TSC_Hildi"
End If

Worksheets("Assumptions").Activate
Range("G5").Select
Application.CutCopyMode = False

Roy Cox

It's a little complicated but let me have a dummy workbook with no sensitive data and the macros that you want to run in it. I'll knock you up an example and some links to how to do it yourself

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

schwientekd

ASKER

A+++++

You're right, this is a bit complicated. I was able to figure out the buttons and code by looking at how you set it up in the example. It looks like I've got it working in my application now. Thanks very much for all of your effort!!!

Roy Cox

Glad we finally got a solution.