How to access group buttons in Class Module?

[Myles]

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

 

[Bob Phillips]

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]

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


Myle

 

[Bob Phillips]

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]

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

 

[Peter T]

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

 

[Bob Phillips]

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

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

You can't actually. I assumed that Myles was creating a collection for the
buttons within the form which he could then address with Item, but he is not
assigning the controls to an array of the class, slightly different
approach. I like the collection myself ).

Bob

 

[Myles]

Thanks Peter. That tweaking worked. I showed a bit of mental laxity b
failing to realise that the Array that created the buttons has
holding name for all the elements. Once again, thanks and same goes t
Bob whose cue I misjudged.

Myles