Form versus Control

[Mark]

Excel has a Control Toolbox and a Forms toolbar.

Both toolbars have a similar icon:
Form has a Button icon for associating a macro,
Control Toolbox has a Control Button that you can copy the code from a
macro into.

I've noticed some code inside a macro will run from the Button but not
the Control Button.

Can anyone help explain this?

Thanks, Mark

 

[Tom Ogilvy]

That is pretty vague Mark. Can you give an example of specific code that
will run in a button click event, but not in a command button event (which
event - assume click). And what you mean by will not run.

If properly structured and qualified, most code should run in either.

 

[Mark]

This is the macro that runs successfully (it's in Module1):
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/14/2003 by Mark Roach
'
Sheets("Sheet2").Select
Worksheets("sheet2").Range("Ak8:BQ20000").Clear
Range("A2:AE99").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _
"AJ2:AJ3"), CopyToRange:=Range("AK8"), Unique:=False
Sheets("sheet3").Select
End Sub

This is the code from Microsoft Excel Objects/Sheet3 that fails to
execute the advanced filter correctly:
Public Sub CommandButton1_Click()
Sheets("Sheet2").Select
Worksheets("sheet2").Range("Ak8:BQ20000").Clear
Range("A2:AE99").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _
"AJ2:AJ3"), CopyToRange:=Range("AK8"), Unique:=False
Sheets("sheet3").Select
End Sub

 

[Jim Rech]

Check the command button's properties by right-clicking it. If the "Take
Focus" option is set try clearing it.

 

[Craig]

I have a related question.... (and I bet Tom O. can answer it!)

I've been told that the embedded Control buttons will consume more
memory than the older-style Form buttons used to call the same
subroutine. Any truth to that and if so, why are the Control buttons
the style of choice these days?

Thanks,
Craig

 

[Ron de Bruin]

Don't know the answer about the memory?

Controls from the Control Toolbox have much more options in the
properties then the Forms controls.

One thing I like is that the control button have a click event instead of assigning a macro to it.
If you copy a sheet to a new workbook for example the button still work
because the click event is in the Sheetmodule.

 

[Tom Ogilvy]

I can't say about the memory as I have ever tested it, but I would suspect
that the Control Toolbox Toolbar controls require more overhead and thus
more memory.

The Control Toolbox Toolbar controls are
Documented,
In the object browser (not hidden)
Written about in books and articles
Offer more control
Appear more object oriented
Are the only controls for userforms (in windows versions)

the forms toolbar controls are largely ignored and undocumented since xl97,
less flexible, don't work on userforms under windows (and who uses dialog
boxes). However, they work nicely in many instances on the worksheet and
are more closely integrated with excel, such as when using a defined name as
a dynamic source for the source of a combobox or listbox.

 

[Tom Ogilvy]

Works with forms buttons as well (if you write the event code correctly),
because the copy of the button still points to the event code.