Change event of Combobox in Userform not getting triggered-CodePudding

I have a userform and a Click button on it. On click of this button a combobox is dynamically created. I want to do something when a particular value is selected from this combobox but the change event is not getting triggered. What could be the reason. Here is my code which is put in the UserForm1 module.

Private WithEvents ComboBox1 As MSForms.ComboBox

Private Sub ClickButton_Click()
      
    'Create  combo box
    Dim ComboBox1 As MSForms.ComboBox
    Set ComboBox1 = Me.Controls.Add("Forms.ComboBox.1")
    With ComboBox1
        .Left = 160
        .Top = 50
        .Width = 70
        .Height = 20
        .AddItem ("> than")
        .AddItem ("< than")
        .AddItem ("Max")
        .AddItem ("Min")
        .Enabled = True
        .BackColor = RGB(255, 255, 255)
        .ForeColor = RGB(0, 0, 0)
        .SpecialEffect = fmSpecialEffectFlat
        .Font.Size = 12
        .Font.Bold = False
        .Font.Name = "Arial"
        .TabIndex = 2
    End With
        
    DoEvents
    ComboBox1.SetFocus
      
End Sub

Private Sub ComboBox1_Change()
    Dim inputNumber As Variant
    If ComboBox1.Value = "> than" Then
        inputNumber = InputBox("Enter a number:")
        'Check if the input is valid number
        If IsNumeric(inputNumber) Then
            ComboBox1.Value = ComboBox2.Value & " " & inputNumber
        Else
            MsgBox "Invalid input"
        End If
    End If
End Sub

CodePudding user response：

The method you need to use is described here: https://stackoverflow.com/a/8986622/9852011 , but for your particular case, here is what you need to do:

This is the code that should be in the module of your UserForm:

Private m_oCollectionOfEventHandlers As Collection

Private Sub UserForm_Initialize()
    Set m_oCollectionOfEventHandlers = New Collection
End Sub

Private Sub CommandButton1_Click()
    Dim ComboBox1 As MSForms.ComboBox
    Set ComboBox1 = Me.Controls.Add("Forms.ComboBox.1")
    With ComboBox1
        .Left = 160
        .Top = 50
        .Width = 70
        .Height = 20
        .AddItem ("> than")
        .AddItem ("< than")
        .AddItem ("Max")
        .AddItem ("Min")
        .Enabled = True
        .BackColor = RGB(255, 255, 255)
        .ForeColor = RGB(0, 0, 0)
        .SpecialEffect = fmSpecialEffectFlat
        .Font.Size = 12
        .Font.Bold = False
        .Font.Name = "Arial"
        .TabIndex = 2
    End With
        
    DoEvents
    ComboBox1.SetFocus
    
    Dim cb1EventHandler As comboboxeventhandler
    Set cb1EventHandler = New comboboxeventhandler
    Set cb1EventHandler.ComboBox = ComboBox1
    m_oCollectionOfEventHandlers.Add cb1EventHandler
      
End Sub

Then, insert a new class module into your project, name it "ComboBoxEventHandler" and put this code into it:

Private WithEvents m_oComboBox As MSForms.ComboBox

Public Property Set ComboBox(ByVal oComboBox As MSForms.ComboBox)
    Set m_oComboBox = oComboBox
End Property

Private Sub m_oComboBox_Change()
    Dim inputNumber As Variant
    With m_oComboBox
        If .Value = "> than" Then
            inputNumber = InputBox("Enter a number:")
            'Check if the input is valid number
            If IsNumeric(inputNumber) Then
                .Value = .Parent.ComboBox2.Value & " " & inputNumber
            Else
                MsgBox "Invalid input"
            End If
        End If
    End With
End Sub

I don't know what "ComboBox2" is but for the sake of this example, I just assumed it is a ComboBox which already exists in the UserForm somewhere.