Excel Forms vs. ActiveX Controls 1

[blue9244]

Just for my edification, when would a programmer choose to use checkboxes (for example) using ActiveX controls vs. Excel Forms controls? Would it be a good rule of thumb to use excel checkboxes whenever possible?

 

[Zathras]

Since no one else has cared to address this issue, I'll offer my two cents worth...

The glib answer is that it depends on the requirements for the application. Another way to say that is it depends on the user interface you want to provide.

With a form, you have more control over what the user can do. If multiple bits of data need to be set at the same time before doing something else, a form will handle that nicely.

Dropping check boxes, etc. directly on the sheet may be appropriate as long as the relationships between the various controls is not tightly coupled.

There are other subtle differences. For example, the tab key can advance between controls on a form. As far as I know, the user must use the mouse to select a control on a sheet. (Although you can assign a keyboard accelerator.)

I'm not sure, but I think that technically, in either case you are using ActiveX controls.

I trust, now that I've stepped into it, you should start to see some responses.

 

[combo]

If you think about placing a control on a spreadsheet and can choose between excel forms and activex controls - it is up to what you want to do next, as they behave slightly differently.
Activex controls have many format options, whlist excel controls very limited or practically none.
Excel list and combo boxes return number on the list, which is nice when you use it for input INDEX or OFFSET formulas. Activex controls return value.
Activex controls is in my opinion the best solution if you use a code to play with them, there are more event options and the access is simpler and similar to forms. Excel forms are satisfying if you need to visually organize input data for further spredsheet calculations.

 

[Nikita6003]

Hey Zathras - never one to resist a challenge ;-)

Use the FORMS toolbar to add the "old-style" Excel controls to a worksheet. they're a throwbach to v95, and have proven to be quite useful for som quick forms I needed. However, as combo's pointed out they're fairly limited in their properties & settings.
Use the CONTROL TOOLBOX toolbar to add Active-X controls to a worksheet. I've been using these controls for some time now, setting up Excel front-ends to Sybase, SQL and Oracle databases. They're quite flexible - check out their properties; they're aimilar (but not the same) as Access/VB controls. But a more useful feature is that they're event-driven, which means you can capture things as user clicks & OnChange events.
Finally, the Active-X controls on Excel forms (added thru' the VBA Editor) are similar but not quite the same; they're more like their VB equivalent than the Excel worksheet ones and therefore are even more flexible.

It's really all down to what you/your users need & want.

Cheers
Nikki

 

[xlbo]

All I'd add to this is that if the usage is relatively simple - go for forms toolbar. If it is more complex, use the controls toolbar.

The only major problem I have with activeX objects is that they take more time to load initially, when opening a workbook (see other threads in this forum for examples)

The other "issue" is that you can get unexpected results from using controls toolbox in that they have faaaaar more properties tand therefore there is more scope for not setting something that may need to be set (like the take focus on click property on command buttons which can play havoc with spreadsheet operations)

Not sure if this was in the original question but being as the subject has been brought up, I very rarely use ANY controls on a worksheet (Forms OR Ctrls Toolbox) I will either use a userform or use Data>Validation boxes instead of combos and cells instead of textboxes etc

HTH Rgds
Geoff

Estne volumen in toga, an solum tibi libet me videre?

 

[Nikita6003]

Geoff,
forgot about the slow startup due to the active-x controls ... apart from that, they're useful in my environment where parts of the batches are started manually during the day, and parts are set off by the Excel OnTime function during my night time.
But nice hint!

Cheers
Nikki
And in answer to yours: Non curo. Utinam omnia legere volumen mea!

 

[blue9244]

Thanks for all your responses! My original question really had to do with speed and stability. This spreadsheet started out as an engineering aid that I developed using Excel 95 and it has grown from there to the monster that it currently is.

Initially all the controls were made using the Excel forms toolbar but eventually I converted all of those to ActiveX controls. But in attempting to simplify and streamline it, I thought I ought to consider reverting back to the simpler controls wherever possible. And, I am converting drop down list boxes to Data Validation in as many places as I can.

And I understand the difference between the two types. I will need to retain some ActiveX controls but feel I can justify use of the simpler variety in some instances. Just really wanted the validation that I got from xlbo above.

Thanks everyone!

 

[xlbo]

Just as a final point - if you have code that references any of the controls, the syntax for referencing forms toolbar controls is different then for controls toolbox controls Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[sdraper]

Wouldn't the activeX controls also need to be installed on the user's box where the forms controls would be intrinsic? A definite consideration in a multi user environment...
sdraper

 

[rmikesmith]

sdraper,

As far as I know, the ActiveX controls are installed during the Excel (or Office) installation. Nothing special needs to be done.


Regards,
Mike