Add same change event to multiple ComboBoxes

**pike** · Apr 23rd 2016

Re: Add same change event to multiple ComboBoxes

Hi
Don't think even Jaafar Tribak could solve that puzzle ..
but I'll have a play around
what is the userform setup?

ivanian · Apr 23rd 2016

Re: Add same change event to multiple ComboBoxes

There are 2 Groups of comboboxes in the form. First group is not inside a frame and populated with a namerange. 2nd Group is in a frame and populated with a different namerange. I included the Worksheet in the attachment. Hopefully, there's a way for it. Thanks again

**pike** · Apr 23rd 2016

Re: Add same change event to multiple ComboBoxes

Hi
a quick hack .. the class will have to be made as an instance of the frame1 and another an instance of the userform1
bit of an initial focus issue but works on the second click in the second combobox

**pike** · Apr 23rd 2016

Re: Add same change event to multiple ComboBoxes

Maybe this ..

Code

Option Explicit
Public Event OnEnter(Ctrl As MSForms.Control)
Public Event OnExit(Ctrl As MSForms.Control, Cancel As Boolean)
Private oXitClass As CtlExitCls
Private bFormUnloaded As Boolean
Private bCancel As Boolean
Private oPrevActiveCtl As MSForms.Control
Private oCol As New Collection
Private oXitClassTwo As CtlExitCls
Private oPrevActiveCtlTwo As MSForms.Control


Private Sub CommandButton1_Click()
    Unload Me
End Sub
Private Sub UserForm_Initialize()
    Me.TextBox7.SetFocus
End Sub
Private Sub UserForm_Layout()
    Call WatchEvents
End Sub
Private Sub UserForm_Terminate()
    Call CleanUp
End Sub
Private Sub WatchEvents()
    
    If Not oXitClass Is Nothing Then Exit Sub
    Set oXitClass = New CtlExitCls
    Set oXitClass.FormCtrl = Me
    bFormUnloaded = False
    Set oPrevActiveCtl = Me("Frame1").ActiveControl
    RaiseEvent OnEnter(Me("Frame1").ActiveControl)
    
    If Not oXitClassTwo Is Nothing Then Exit Sub
    Set oXitClassTwo = New CtlExitCls
    Set oXitClassTwo.FormCtrl = Me
    bFormUnloaded = False
    Set oPrevActiveCtlTwo = Me.ActiveControl
    RaiseEvent OnEnter(Me.ActiveControl)
    
    Do While bFormUnloaded = False
        If Not oPrevActiveCtl Is Nothing Then
            If Not oPrevActiveCtl Is Me("Frame1").ActiveControl Then
                RaiseEvent OnExit(oPrevActiveCtl, bCancel)
                RaiseEvent OnEnter(Me("Frame1").ActiveControl)
                If bCancel Then
                    oPrevActiveCtl.SetFocus
                Else
                    Me("Frame1").ActiveControl.SetFocus
                End If
            End If
        End If
        Set oPrevActiveCtl = Me("Frame1").ActiveControl
        DoEvents
        If Not oPrevActiveCtlTwo Is Nothing Then
            If Not oPrevActiveCtlTwo Is Me.ActiveControl Then
                RaiseEvent OnExit(oPrevActiveCtlTwo, bCancel)
                RaiseEvent OnEnter(Me.ActiveControl)
                If bCancel Then
                    oPrevActiveCtlTwo.SetFocus
                Else
                    Me.ActiveControl.SetFocus
                End If
            End If
        End If
        Set oPrevActiveCtlTwo = Me.ActiveControl
        DoEvents
    Loop
End Sub

Display More

Add same change event to multiple ComboBoxes

Participate now!

Share