Controls

[Spike]

Probably a very basic question but when placing say Buttons, Combo boxes etc
on a spreadsheet when should one use Form controls and when should one use
Control Toolbox controls.

 

[Jim Thomlinson]

Not a basic question at all. Here is a QND explanation...

Control Toolbox - This is the button I use 99% of the time. It is the most
familliar to people who are familliar with coding in VB. A button is embeded
right in the sheet and the code written lands in the sheet (not in a module).
Drag a button off the control toolbox and right click on it. Select
Properties
and here you can modify the look and function of the button. There are lots
of
choices. You definitely want to change name to something like cmdMyButton
and the caption to a description of the action the button does, like "Copy
Stuff". Right click on the button again and choose view code. Now you can
write code for the button to do.

Forms Toolbar - The button from this menu is more like a picture of a
button. When you add the button you will be promted to link it to a macro. It
could be any publicly declared procedure written in a module (recorded macros
qualify here). That can be handy if you are wanting to copy sheets out of
your workbook and have no code in the sheet.

A couple of notes. I have had at least one or 2 instances where buttons from
the control toolbox causes weird things to start happening. In one instance
it caused my app to randomly crash and in the other it caused weird side
effects in other open workbooks. That is 2 instances in many years of coding.

 

[Dave Peterson]

Just to muddy the water...

I almost always use the controls from the Forms toolbar. They are less
customizable, but they also behave better when there are lots of them on the
worksheet.

And I can assign the same macro to different controls--even on different
sheets.

One of the times that I'll use controls from the Control toolbox toolbar is when
I know that I'll be copying/moving that sheet to another workbook--and that
control has code associated with it.

If I keep all the code in the worksheet module, then that code will move with
the worksheet to its new home.

Controls from the Forms toolbar that have macros assigned will still point back
to the original workbook. And usually will need to have the macros assigned to
point at the new workbook.

 

[Spike]

Thank you Dave much appreciated, sorry been away hence tardiness in reply

 

[Spike]

Thank you Jim much appreciated, sorry been away hence tardiness in reply