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.
schwientekdIT 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.

Roy CoxGroup Finance ManagerCommented:
That code should not cause any version issues.

Can you attach an example file?
0
schwientekdIT DirectorAuthor Commented:
Sorry I cannot post the file.
0
Roy CoxGroup Finance ManagerCommented:
What is the full code in one of the problem buttons?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

schwientekdIT DirectorAuthor Commented:
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
0
Roy CoxGroup Finance ManagerCommented:
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

0
schwientekdIT DirectorAuthor Commented:
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.
0
Roy CoxGroup Finance ManagerCommented:
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
0
schwientekdIT DirectorAuthor Commented:
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)
0
Roy CoxGroup Finance ManagerCommented:
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.
0
schwientekdIT DirectorAuthor Commented:
Nothing seems to work.  Not sure what else to try.
0
Roy CoxGroup Finance ManagerCommented:
I've found this that might help

Resizing Buttons Quirk
0
schwientekdIT DirectorAuthor Commented:
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.
0
Roy CoxGroup Finance ManagerCommented:
It really doesn't make sense, I can't think of anything else, but I'll keep looking.
0
Roy CoxGroup Finance ManagerCommented:
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?
0
schwientekdIT DirectorAuthor Commented:
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
0
Roy CoxGroup Finance ManagerCommented:
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
0
Roy CoxGroup Finance ManagerCommented:
I've added a new tab with a button to run the macro.  I used a free tool created by Andy Pope to do this.

RibbonX Visual Designer

Another useful tool is the Custom UI Editor Tool for editing the xml. I have also an image to the button using the CustomUI Editor, theres' an image icon that allows you to browse for suitable images. See the CustomUI xml below.

<!--RibbonX Visual Designer 2.31 for Microsoft Excel CustomUI . XML Code produced on 2015/07/24-->
<customUI  xmlns="http://schemas.microsoft.com/office/2006/01/customui" >
	<ribbon >
		<tabs >
			<tab 
				id="MyTab"
				label="My Tab">
				<group 
					id="Group1"                              
					label="My Group">
					<button 
						id="btn1"
						label="My Button"
						image="verification12"	
						supertip="Click me to run the macro"
						size="large"
						onAction="RBN_CallControl"/>
				</group >
			</tab >
		</tabs >
	</ribbon >
</customUI >

Open in new window



If you download Andy'd tool and use it to open the example workbook you will be aBle to see the properties of the button and edit them.


Here's the code  that is run by clicking the button, note this code can be expanded to work with several buttons

Option Explicit

Private Sub RBN_CallControl(control As IRibbonControl)
''///this code is run by the button(s), note it can be used for several buttons and
''///and identifies the correct code from the control ID
    Select Case control.ID
    Case Is = "btn1"
        Call MyMacro
'    Case Is = "Button2"
'        Call ImportForm
'    Case Is = "Button3"
'        Call CreateNew
'    Case Is = "Button4"
'        UpdateLog
'    Case Is = "Button5"
'        SendEmail
    End Select
End Sub

Open in new window


Finally you need to move the code from the button on the sheet to a Standard module, see the module mMain.

Check it out and post back if you need help implementing this.
ee-macro-sample.xlsm
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
schwientekdIT DirectorAuthor Commented:
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!!!
0
Roy CoxGroup Finance ManagerCommented:
Glad we finally got a solution.
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
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.