I have a Class Event that controls a group of CommandButtons and set
up as follows in a Class Module (named BnClass).

Public WithEvents ButtonGroup As Msforms.CommandButton
Sub ButtonGroup_Click()

<code

End sub

I have no difficulty accessing any of the 60 buttons involved from the
Userform module or a general module but in either instance only through
the facility of a
*For each ... Next* construct which enables me to tap into the
controls collection.

Now, my problem is to get the right syntax to access *only one* of the
buttons without having to resort to For each ... next Loop. For
example, in the Userform module, I attempted (and crashed!) in a bid
to re-assign an attribute of CommandButton20 from the group to another
control (CmdPivot). None of the following worked with each throwing up
"Object Required" error.

Me.CmdPivot.BackColor = BnClass.ButtonGroup(CommandButton20).BackColor
Me.CmdPivot.BackColor = BnClass.(CommandButton20).BackColor
Me.CmdPivot.BackColor = CommandButton20.BackColor

Can someone kindly point me the way?

[ I want to avoid the verbosity of using a loop as in: For each ctl
in Me.Controls
If Typename(ctl) ="CommandButton" Then
If ctl.Name="CommandButton20 Then
< code
End if
End if
Next
This is because I have a number of such and similar assignments to make
here and there (18 in total) and would imagine that applying looping
each time will be unwieldy.]


Thanks

Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=492389

I have tried to change my tests to your names etc., and this works for me

Me.CmdPivot.BackColor = BnClass.Item(6).mButtonGroup.BackColor

where BnClass is the name of the collection in the form, and mButtonGroup is
the name of the public withevents button variable in the class.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Myles" wrote in
message ...

I have a Class Event that controls a group of CommandButtons and set
up as follows in a Class Module (named BnClass).

Public WithEvents ButtonGroup As Msforms.CommandButton
Sub ButtonGroup_Click()

<code

End sub

I have no difficulty accessing any of the 60 buttons involved from the
Userform module or a general module but in either instance only through
the facility of a
*For each ... Next* construct which enables me to tap into the
controls collection.

Now, my problem is to get the right syntax to access *only one* of the
buttons without having to resort to For each ... next Loop. For
example, in the Userform module, I attempted (and crashed!) in a bid
to re-assign an attribute of CommandButton20 from the group to another
control (CmdPivot). None of the following worked with each throwing up
"Object Required" error.

Me.CmdPivot.BackColor = BnClass.ButtonGroup(CommandButton20).BackColor
Me.CmdPivot.BackColor = BnClass.(CommandButton20).BackColor
Me.CmdPivot.BackColor = CommandButton20.BackColor

Can someone kindly point me the way?

[ I want to avoid the verbosity of using a loop as in: For each ctl
in Me.Controls
If Typename(ctl) ="CommandButton" Then
If ctl.Name="CommandButton20 Then
< code
End if
End if
Next
This is because I have a number of such and similar assignments to make
here and there (18 in total) and would imagine that applying looping
each time will be unwieldy.]


Thanks

Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=492389

Many thanks Bob for the "tip-off". I will try your solution (which look
good to the eye) and report back.


Myle

--
Myle
-----------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...fo&userid=2874
View this thread: http://www.excelforum.com/showthread.php?threadid=49238

Oh yes, just noticed, I used item(6), not Item(20) as I created fewer
buttons to start with :-))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Myles" wrote in
message ...

Many thanks Bob for the "tip-off". I will try your solution (which looks
good to the eye) and report back.


Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=492389

Hi Bob,

I am not out of the woods yet. I still get the OBJECT REQUIRED error.
No doubt, I must be doing something wrong.

Just to highlight my problem, I have code that create 20 CommandButtons
on the fly with these consigned to a class group. There is a separate
CommandButton (named CmdSubmit) which is created "manually" at design
time. The idea now is to change the backcolor of this button to the
backcolor of one of the 20 group buttons (if certain conditions are
met). It seems frivolous to carry out this exercise but the object is
to parody a far more serious operation that would be embarked upon if
the principle is learnt. Now, my codes:

IN USERFORM MODULE:

Dim buttons() As New BnClass
Private Sub UserForm_Initialize()
n = 1
For i = 1 To 4
For j = 1 To 5
Set y = Me.Controls.Add("Forms.CommandButton.1")
With y
..Top = 0
..Height = 50
..Top = 2 + .Height * (i - 1)
..Width = 50
..Left = 2 + .Width * (j - 1)
..BackColor = vbYellow
If n Mod 8 = 0 Then .BackColor = vbRed
..Caption = n
End With
n = n + 1
Next
Next
Call CreateButtonclass
Call test
End Sub
Sub CreateButtonclass()
For Each ctl In Me.Controls
If TypeName(ctl) = "CommandButton" Then
n = n + 1
ReDim Preserve buttons(n)
Set buttons(n).ButtonGroup = ctl
End If
Next
End Sub

Sub test()
'Color CmdSumitBackColor red as CommandButton8 in the group
CmdSubmit.BackColor = BnClass.Item(8).ButtonGroup.BackColor --
*Object required error here*
End Sub

IN CLASS MODULE NAMED -BNCLASS-

Public WithEvents ButtonGroup As MsForms.CommandButton

Sub ButtonGroup_Click()
MsgBox ButtonGroup.Name
End Sub


Thanks


Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=492389

As Bob is not here at the moment -

If "buttons" is the name of the array and "BnClass" the class name then
change

CmdSubmit.BackColor = BnClass.Item(8).ButtonGroup.BackColor
to
CmdSubmit.BackColor = buttons(8).ButtonGroup.BackColor
'could include .Item(8) for clarity

In passing I think you want dots before all the properties relating to "With
y"

Regards,
Peter T

"Myles" wrote in
message ...

Hi Bob,

I am not out of the woods yet. I still get the OBJECT REQUIRED error.
No doubt, I must be doing something wrong.

Just to highlight my problem, I have code that create 20 CommandButtons
on the fly with these consigned to a class group. There is a separate
CommandButton (named CmdSubmit) which is created "manually" at design
time. The idea now is to change the backcolor of this button to the
backcolor of one of the 20 group buttons (if certain conditions are
met). It seems frivolous to carry out this exercise but the object is
to parody a far more serious operation that would be embarked upon if
the principle is learnt. Now, my codes:

IN USERFORM MODULE:

Dim buttons() As New BnClass
Private Sub UserForm_Initialize()
n = 1
For i = 1 To 4
For j = 1 To 5
Set y = Me.Controls.Add("Forms.CommandButton.1")
With y
Top = 0
Height = 50
Top = 2 + .Height * (i - 1)
Width = 50
Left = 2 + .Width * (j - 1)
BackColor = vbYellow
If n Mod 8 = 0 Then .BackColor = vbRed
Caption = n
End With
n = n + 1
Next
Next
Call CreateButtonclass
Call test
End Sub
Sub CreateButtonclass()
For Each ctl In Me.Controls
If TypeName(ctl) = "CommandButton" Then
n = n + 1
ReDim Preserve buttons(n)
Set buttons(n).ButtonGroup = ctl
End If
Next
End Sub

Sub test()
'Color CmdSumitBackColor red as CommandButton8 in the group
CmdSubmit.BackColor = BnClass.Item(8).ButtonGroup.BackColor --
*Object required error here*
End Sub

IN CLASS MODULE NAMED -BNCLASS-

Public WithEvents ButtonGroup As MsForms.CommandButton

Sub ButtonGroup_Click()
MsgBox ButtonGroup.Name
End Sub


Thanks


Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=492389