Single Class and EventHandler for multiple Control types in Userform?

[ClimoC]

Howdy gang

Trying to do a shortcut - Userform is having dynamic number of items created at runtime for the user to interact with.

I've done this successfully with Checkboxes, and that's all fine - but I was as I'm adding a lot of textboxes and comboboxes, and they all need an event fired on _Change(), I thought I'd be able to create a Custom Class of type 'MSForms.Control', and then from a Case statement fire the right event (using the convention-named Name of the control for its type) - but I'm hitting walls.

The following bugs as "Error 459 - Object or Class does not support the set of events" - which I suppose makes sense, on the indicated line below:

'Class Module code (class name is 'clsSportFixEdit'):

Public WithEvents EditCntrl As MSForms.control
Public gID As Long


Private Sub EditCntrl_Change()
If Not Application.EnableEvents = False Then
            With VersionDetailForm
                Select Case True
                        Case EditCntrl.Name Like "*Sport*"
                                'Call Something
                        Case EditCntrl.Name Like "*VerNum*"
                                Call CheckVers


                        Case Else
                End Select
            End With
End If


End Sub

And in the Userform itself:

Private mVDFElementEvents(1 To (Ver_Fix_Num * 10)) As clsSportFixEdit

'and a function is called from Initialize() 

Function MakeSportsEditFixtures(ByVal idx As Long)


Dim myTop As Long, elemID As Long, elemCnt As Long
myTop = (idx - 1) * 78


elemID = (idx - 1) * 10
'Increment, but start at 0
elemCnt = 0




'Main Controls
With Me.Frame1
        elemCnt = elemCnt + 1
        Set mVDFElementEvents(elemID + elemCnt) = New clsSportFixEdit
'//BUGS ON THIS LINE
        Set mVDFElementEvents(elemID + elemCnt).EditCntrl = .Controls.Add("Forms.Textbox.1", "V" & idx & "Event", True)   
'//
        With mVDFElementEvents(elemID + elemCnt).EditCntrl
                    .Top = myTop + 18
                    .Height = 18
                    .Width = 126
                    .Left = 48
        End With
        
        elemCnt = elemCnt + 1
        Set mVDFElementEvents(elemID + elemCnt) = New clsSportFixEdit
        Set mVDFElementEvents(elemID + elemCnt).EditCntrl = .Controls.Add("Forms.Combobox.1", "V" & idx & "Type", True)
        With mVDFElementEvents(elemID + elemCnt).EditCntrl
                .Top = myTop + 18
                .Height = 18
                .Width = 90
                .Left = 180
        End With

End with

So, is there any way of tweaking this to place a path for all _Change() events to go (as Textboxes and Comboboxes both have _Change Events) - or am I barking up the wrong tree because a generic 'Control' doesn't have an Event for that?

Am I better off just making 2 classes - one for the Textboxes and one for the Comboboxes?

Thanks

 

[Jaafar Tribak]

Why don't you just use two WithEvents declartions in the class module as follows instead of using one for all controls ?:

[COLOR=#333333]Public WithEvents [/COLOR][COLOR=#333333]EditCntrl [/COLOR][COLOR=#333333]As MSForms.TextBox[/COLOR]
[COLOR=#333333]Public WithEvents ComboCntrl As MSForms.ComboBox[/COLOR]

If you still want to use a generic event you can take the API route .

example:

The code in the following example will add 4 textboxes and 4 ComboBoxes at runtime and will hook their respective change events... all textboxes and comboboxes will share the same change event handler located at the bottom of the form module (CtrlChangeEvent).

Note:
In order for the code to take effect, you will have to save and close the workbook first.... The code should then work as expected when you re-open the workbook.

-Code goes in the UserForm Module:

Option Explicit

Private Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(0 To 7) As Byte
End Type

[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function ConnectToConnectionPoint Lib "shlwapi" Alias "#168" (ByVal punk As stdole.IUnknown, ByRef riidEvent As GUID, ByVal fConnect As Long, ByVal punkTarget As stdole.IUnknown, ByRef pdwCookie As Long, Optional ByVal ppcpOut As LongPtr) As Long
    Private Declare PtrSafe Function IIDFromString Lib "ole32.dll" (ByVal lpsz As LongPtr, lpiid As GUID) As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
    Private Declare Function ConnectToConnectionPoint Lib "shlwapi" Alias "#168" (ByVal punk As stdole.IUnknown, ByRef riidEvent As GUID, ByVal fConnect As Long, ByVal punkTarget As stdole.IUnknown, ByRef pdwCookie As Long, Optional ByVal ppcpOut As Long) As Long
    Private Declare Function IIDFromString Lib "ole32.dll" (ByVal lpsz As Long, lpiid As GUID) As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If


Private Const IID1 = "{8BD21D12-EC42-11CE-9E0D-00AA006002F3}" 'TextBox iid.
Private Const IID2 = "{8BD21D32-EC42-11CE-9E0D-00AA006002F3}" 'cOMBObOX iid.
Private Const S_OK = 0&
 
Private tIID As GUID
Private oCtrl As Object
Private lCookie As Long


Private Sub UserForm_Initialize()

    Dim i As Long
    Dim oTextBox As Control, oComboBox As Control
    
    For i = 1 To 4
    
        Set oTextBox = Controls.Add("Forms.TextBox.1")
        Call HookControl(oTextBox, True)
        With oTextBox
            .Name = "TextBox" & i
            .Height = 20
            .Width = 50
            .Left = 70
            .Top = 20 * i * 1
        End With
        
        Set oComboBox = Controls.Add("Forms.ComboBox.1")
        Call HookControl(oComboBox, True)
        With oComboBox
            .Name = "ComboBox" & i
            .Height = 20
            .Width = 50
            .Left = 150
            .Top = 20 * i * 1
        End With
        
    Next i
    
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    Dim oCntrl As Control
    
    For Each oCntrl In Me.Controls
        If InStr(1, oCntrl.Tag, "Hooked") Then
            Call HookControl(oCtrl, False)
        End If
    Next oCntrl

End Sub


Private Sub HookControl(ByVal Ctrl As Control, ByVal Hook As Boolean)

    Static i As Long
    Dim tIID As GUID, lRet As Long
    
    If Hook Then
        If TypeName(Ctrl) = "TextBox" Then
            lRet = IIDFromString(StrPtr(IID1), tIID)
            If lRet = S_OK Then
                Call ConnectToConnectionPoint(Me, tIID, 1, Ctrl, lCookie)
            End If
        ElseIf TypeName(Ctrl) = "ComboBox" Then
            lRet = IIDFromString(StrPtr(IID2), tIID)
            If lRet = S_OK Then
                Call ConnectToConnectionPoint(Me, tIID, 1, Ctrl, lCookie)
            End If
        End If
        Ctrl.Tag = "Hooked"
        i = i + 1
    Else
        Call ConnectToConnectionPoint(Nothing, tIID, 0, Ctrl, lCookie)
    End If

End Sub



[B][COLOR=#008000]'GENERIC CHANGE EVENT:[/COLOR][/B]
[B][COLOR=#008000]'====================[/COLOR][/B]
Public Sub CtrlChangeEvent()
[B][COLOR=#008000]   'Attribute CtrlChangeEvent.VB_UserMemId = 2
[/COLOR][/B]
    Attribute CtrlChangeEvent.VB_UserMemId = 2
    MsgBox "You Changed : " & Me.ActiveControl.Name
End Sub