Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.onaction property for controls checkbox
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. -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
.onaction property for controls checkbox
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) "excel_stuck via OfficeKB.com" <u23938@uwe wrote in message news:64a9c6dbabd27@uwe... 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. -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
.onaction property for controls checkbox
Thanks Bob, but do you know how to assign the macro programatically?
Bob Phillips wrote: 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. 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200608/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
.onaction property for controls checkbox
It is already assigned - it just has to exist or be written.
http://www.cpearson.com/excel/vbe.htm -- Regards, Tom Ogilvy "excel_stuck via OfficeKB.com" <u23938@uwe wrote in message news:64ae2af3c859d@uwe... Thanks Bob, but do you know how to assign the macro programatically? Bob Phillips wrote: 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. 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200608/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
.onaction property for controls checkbox
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 "excel_stuck via OfficeKB.com" <u23938@uwe wrote in message news:64a9c6dbabd27@uwe... 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. -- Message posted via http://www.officekb.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
.onaction property for controls checkbox
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. NickHK wrote: 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 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200608/1 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
.onaction property for controls checkbox
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) "excel_stuck via OfficeKB.com" <u23938@uwe wrote in message news:64c0ac58be97b@uwe... 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. NickHK wrote: 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 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200608/1 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
.onaction property for controls checkbox
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. "excel_stuck via OfficeKB.com" wrote: 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. NickHK wrote: 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 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200608/1 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting OnAction Property Fails | Excel Programming | |||
OnAction-property of cbbutton in VBE editor | Excel Programming | |||
Button Selection OnAction property | Excel Programming | |||
Find OnAction property | Excel Programming |