Link to home
Create AccountLog in
Avatar of Roger
RogerFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Excel Active X control buttons in distributed excel pieces: are there downsides/ risks to Active X controls supplied by Microsoft?

I am considering using excel sheets and EXCEL ActiveX buttons to actuate some vba processes, instead of using UserForms.
Two reasons:
1. for catalog situations where I need one or more control buttons per data row
2. for situations where I have a child window occupying half of the excel application window (eg a child visio window) and the other half of the excel application window can display part of an excel worksheet, on which I can display data and 'buttons' to present/mimic an interactive data panel.

In both these situations, I need buttons on my worksheet.

My question is: Are Active X controls SAFE? What are there downsides? The piece is intended for many users, and potentially in web applications - so will I regret using these Active X controls down the line?
- For single 'cmd' button purposes, I can create one or more standard graphics as clickable objects through which to call macros, instead of ActiveX cmd buttons. Would you consider that positively, or are Active X cmd buttons the best choice?
- Clearly Active X provides more functionality via ComboBox/spin button/other/ controls, but are these ctls safe for distribution?

I should add that I dont envisage the excel sheet being zoomed by large amounts, incase that affects the positioning of Active X ctls?

.. looking before I leap.

Thanks
Kelvin
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

I would always use buttons from the Forms selection, or maybe shapes with the code attached.
Avatar of Roger

ASKER

Thank you -

- I use VBA methods to add buttons (from the forms selection) to the UserForm, and to link them to macros, etc.
--- Are those same VBA methods sufficient to add buttons (from the forms selection) to excel SHEETS?

Kelvin
I'm not sure what you mean. ActiveX controls on a worksheet can be problematic which is why I stick to the Forms version (not UserForm buttons)


Forms Button
You can add a Forms control button in much the same way as an ActiveX button.
•      On the Developer tab, in the Controls group first click Insert, and then under Form Controls click the Button icon in Forms Controls group
•      Click the worksheet location where you want the upper-left corner of the button to appear. Use the drag handles to draw your button. You can change the size and position later.
•      The Assign Macro dialog box will immediately appear, giving you the opportunity to assign a macro to the button. You will see that the dialog box shows a list of previously created macros, along with a suggested name for the macro to be assigned to this button. The suggested name is comprised of the default name of the button itself (something like Button1) combined with the action that will start the macro (Click).
•      To finish with the Assign Macro dialog box, select a macro you want assigned to this new button and then click on OK. You can then change the caption appearing on the button by clicking your mouse within the button text and entering a new caption.
•      You have now created a button for your macro which will be run whenever anyone clicks on it with the left mouse button.
•      If you use the right mouse button instead, you will see a menu that allows you to delete the button or change the macro assigned to the button.
•      To specify the control properties of the button, right-click the button, and then click Format Control. A menu that allows you to delete the button or change the macro assigned to the button will appear. You can also change various Properties of the button here- Size, Text, Font, etc. The Properties tab is very useful, here you can choose whether the button will print or not and positioning of the control.
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Roger

ASKER

Many thanks; I have them working.
Kelvin