Class Module for ComboBoxes AND OptionButtons. Seeking advice: How to include both?

[melpa]

Hello all,

I come as a novice seeking advice from the amazing minds I see here!

Guided by this code:

'   Class module named TBClass

Public WithEvents TBGroup As MSForms.TextBox

Private Sub TBGroup_Change()
    MsgBox TBGroup.Name & " changed"
End Sub

'   UserForm module

Dim TBs() As New TBClass

Private Sub UserForm_Initialize()
    Dim TBCount As Integer
    Dim Ctrl As Control
    TBCount = 0
    For Each Ctrl In UserForm1.Controls
        If TypeName(Ctrl) = "TextBox" Then
            TBCount = TBCount + 1
            ReDim Preserve TBs(1 To TBCount)
            Set TBs(TBCount).TBGroup = Ctrl
        End If
    Next Ctrl
End Sub

Posted by Andrew Poulsom here:
http://www.mrexcel.com/forum/excel-questions/65901-multiple-textboxes-same-code.html
- I have successfully applied my first Class module to a group of ComboBoxes.

Now I would like to somehow include a group of 4 OptionButtons (all in one frame) in that same Class module: When any of the OptionButtons is selected, that should set off the exact same procedure as the change event of the ComboBoxes.

I don't know how to achieve this and am hoping for some guidance.

I have tried to follow the same piece of code and apply it to OptionButtons: I created a new Class module, but I get lost when it comes to the code that would go in the UserForm module.
Besides, it seems repetitive - which was the very thing I set out to remedy - and I suspect there is a simpler and more elegant way to go about it: somehow including OptionButtons in the code already provided.

In anticipation, and with advance gratitude,
Cheers,
Melpa

 

[pike]

Hi Melpa
its complicated as Jaafar Tribak explains here
http://www.mrexcel.com/forum/excel-questions/519492-cannot-setfocus-userform-control.html
also for a example download workbook try
http://yoursumbuddy.com/userform-event-class-multiple-control-types/

 

[MickG]

Try this for "TextBoxes" , "Option Buttons" & "Comboboxes"
Select as required !!!
There's Possibly other ways to do this, but this worked for me !!!!!

Option Explicit
''   Class module named TBClass
Option Explicit
Public WithEvents CBGroup As MSForms.ComboBox
Public WithEvents TBGroup As MSForms.TextBox
Public WithEvents OBGroup As MSForms.OptionButton

Private Sub TBGroup_Change()
MsgBox TBGroup.Name & " changed"
End Sub
Private Sub OBGroup_Click()
MsgBox OBGroup.Name & " changed"
End Sub
Private Sub CBGroup_Change()
MsgBox CBGroup.Name & " changed"
End Sub

'#################################
''   UserForm module
Dim CBs() As New TBclass
Dim OBs() As New TBclass
Dim TBs() As New TBclass

Private Sub UserForm_Initialize()
'For Comboboxs
    Dim CBCount As Integer
    Dim cCtrl As Control
    CBCount = 0
    For Each cCtrl In UserForm1.Controls
        If TypeName(cCtrl) = "ComboBox" Then
            CBCount = CBCount + 1
            ReDim Preserve CBs(1 To CBCount)
            Set CBs(CBCount).CBGroup = cCtrl
        End If
    Next cCtrl

'For OptionButtons
Dim OBCount As Integer
    Dim oCtrl As Control
    OBCount = 0
    For Each oCtrl In UserForm1.Controls
        If TypeName(oCtrl) = "OptionButton" Then
            OBCount = OBCount + 1
            ReDim Preserve OBs(1 To OBCount)
            Set OBs(OBCount).OBGroup = oCtrl
        End If
    Next oCtrl
 
 'For TextBoxes
 Dim TBCount As Integer
    Dim Ctrl As Control
    TBCount = 0
    For Each Ctrl In UserForm1.Controls
        If TypeName(Ctrl) = "TextBox" Then
            TBCount = TBCount + 1
            ReDim Preserve TBs(1 To TBCount)
            Set TBs(TBCount).TBGroup = Ctrl
        End If
    Next Ctrl

End Sub

Regrd Mick

 

[MickG]

This is the condensed Version of the Userform Code.

Dim CBs() As New TBclass
Dim OBs() As New TBclass
Dim TBs() As New TBclass


Private Sub UserForm_Initialize()
Dim Ctrl As Control
Dim TBCount As Integer
TBCount = 0
Dim OBCount As Integer
OBCount = 0
Dim CBCount As Integer
CBCount = 0
 
 For Each Ctrl In UserForm1.Controls
        Select Case True
        Case TypeName(Ctrl) = "TextBox"
            TBCount = TBCount + 1
            ReDim Preserve TBs(1 To TBCount)
            Set TBs(TBCount).TBGroup = Ctrl
        Case TypeName(Ctrl) = "ComboBox"
            CBCount = CBCount + 1
            ReDim Preserve CBs(1 To CBCount)
            Set CBs(CBCount).CBGroup = Ctrl
        Case TypeName(Ctrl) = "OptionButton"
            OBCount = OBCount + 1
            ReDim Preserve OBs(1 To OBCount)
            Set OBs(OBCount).OBGroup = Ctrl
        End Select
        Next Ctrl
End Sub

 

[melpa]

Thanks very much MickG, I'll give your code a go.

And thanks to you too, Pike. You were right though; it was complicated. I was in over my head there!

I appreciate you both taking the time.

Cheers,
Melpa