.onaction property for controls checkbox

  • Thread starter Thread starter excel_stuck via OfficeKB.com
  • Start date Start date
E

excel_stuck via OfficeKB.com

Can anyone tell me how to set the .onaction property for a controls checkbox?
I can do it for a forms check box, but can't find how to do it for a forms
one. Is it possible? Thanks.
 
You click the design icon on the controls toolbar ( a blue-green triangle),
and then you double-click the object which takes you to the worksheet code
pane with a Click event for it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Thanks Bob, but do you know how to assign the macro programatically?
 
As I understand it:
The controls on "Controls Toolbox" are ActiveX or basically Windows
components that Excel can use. Windows "understands" these controls and
hence can raise numerous events (_Click, _Change, _MouseOver etc) when you
perform actions.
The controls on Forms are native to and controlled by Excel/Office. As
such, Windows does not know anything about them and consequently there are
no events for these. Only the single OnAction macro can be assigned.
From your description, you need to look at the _Click event of your
checkbox.

NickHK
 
Thanks Nick-

That helps a lot to understand what's going on and why. I could not
understand why some features of the "controls" checkboxes were not available
for the "forms" checkboxes. You've explained that. The trouble has been
that I have created many check boxes within a macro and need to assign an
action to each of them. A generic routine assigned by .onaction would be
simple. Alternatively, a _Click event would work if the number of checkboxes
was fixed, but, unfortunately, this is not the case. It sounds from Tom's
email that my best option will be to create the subroutines with code (as the
check boxes get created), which seems a little awkward. I'll give that a try.
Thanks to all for your help.
 
Use an application event for the controls.

Add a class module, call it clsWsCtls, with this code as a simple example of
what it can do

Option Explicit

Public WithEvents mCheckboxes As MSForms.CheckBox

Private Sub mCheckboxes_Click()
Dim iCb As Long
If mCheckboxes.Value Then
MsgBox mCheckboxes.Caption & " set"
Else
MsgBox mCheckboxes.Caption & " unset"
End If
End Sub


and add this to the worksheet code module

Option Explicit

Dim mcolEvents As Collection

Private Sub Worksheet_Activate()
Dim cCBEvents As clsWSCtls
Dim shp As Shape

Set mcolEvents = New Collection

For Each shp In Me.Shapes
If shp.Type = msoOLEControlObject Then
If TypeOf shp.OLEFormat.Object.Object Is MSForms.CheckBox Then
Set cCBEvents = New clsWSCtls
Set cCBEvents.mCheckboxes = shp.OLEFormat.Object.Object
mcolEvents.Add cCBEvents
End If
End If
Next

End Sub





--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Another alternative would be to change your code that creates the checkboxes to
use the checkboxes from the Forms toolbar.

You could delete the checkboxes from control toolbox tooblar and replace them
with checkboxes from the forms toolbar, too.
 
Back
Top