I was working on a Sheet and my buttons would no longer let me click them to allow the code to run. I tried to hit the start button in the code window and nothing. I reverted to old saved files and still the buttons will not work. I quit excel and have restarted my computer but none of my spreadsheets with ActiveX buttons will work. I also am unable to add a new ActiveX button as I get a Message Can not insert Object.
Does anyone know what I could have done? Or how I can fix this?
Thanks
Hi Broken-Active-X person;
The easiest way to fix this is as follows:
a) Remove "Private" from all Excel VBA Subroutines which are executed by the Active-X buttons, (I remove all "Private" from the subroutines).
>> Note: Removal of "Private" exposes the VBA Macros to the Forms-Control buttons for assignment.
b) Replace your Active-X buttons with Forms-Control Buttons.
c) Assign the Forms-Control Button to the VBA Macro which used to be executed by the Active-X button.
All of my Excel Tools with VBA macros work just fine after such a change.
Notes:
1) Forms Control buttons DO NOT allow background colors or ability to disable/enable.
2) Forms Control buttons DO allow the ability to "hide" the button with the ".Visible" property, (i.e. ActiveSheet.Buttons("Button 24").Visible = False)
3) For button colors to show the logical buttons that are "in-play" vs. "out-of-play", I'll use a cell or merged set of cells to contain each button.
Then as I click a button that let's say "Starts" a function, I don't with the user to press "Start" a second time before pressing the "Stop" button.
So after the START button is pressed, I'll hide the button "xxx.Buttons("Button 99").Visible = False" and then turn the background cell color to Grey and turn the STOP button containing cell color to green.
wsAHTCntlPage.Cells(5, "C").Interior.ColorIndex = 48 '<== color index for grey wsAHTCntlPage.Cells(5, "F").Interior.ColorIndex = 4 '<== color index for green
I'll place text in the cell to match the button text name I gave to the button, (so the user will know what button has been hidden for context).
This works well and I've found to be the best solution unless MicroSoft has published a simple solution to allow the use of Active-X buttons after their Security Patch.
- Bill Strahl (NoahBodie_Spechell)
