Add same change event to multiple ComboBoxes

  • Following my previous post, I have managed to make a Function that populates multiple comboboxes from a same source and remove items that already exist in other ComboBoxes. Now my problem is I have to call the Function to each comboboxes which makes it redundant. I'm looking into firing the same change event to multiple comboboxes at once.


    This is what my code looks like so far.



    I've seen some approach using Collections but I can't seem to figure it out as to how should I apply the same method that suits my needs.


    Any help is greatly appreciated.


    Thanks and regards,

  • Re: Add same change event to multiple ComboBoxes


    Hi ivanianoh,


    This is one way with a hacked generic userform class event code from Jaafar Tribak


    The Userform1 code


    add a class module named CtlExitCls
    CtlExitCls code

    Code
    Option Explicit
    Public WithEvents FormCtrl As UserForm1
    Private Sub FormCtrl_OnEnter(Ctrl As MSForms.Control)
        Call UserForm1.combobox_list(Ctrl)
    End Sub
    Private Sub FormCtrl_OnExit(Ctrl As MSForms.Control)
    End Sub


    think it is what you want ... hope it helps

  • Re: Add same change event to multiple ComboBoxes


    Hello Pike,


    Exactly what I needed. Thank you so much. Is there a thread where the code came from? I am hoping that I could study the code so I could understand it well.


    And another thing. Do you happen to know what could be the problem with the code below? The change event won't execute. It's a class that I found online thatI modified a bit.


    'clsObjHandler' Class


    UserForm1 Module


    I hope you don't mind. Just trying to learn as much as I can.


    Thanks in advance.

  • Re: Add same change event to multiple ComboBoxes


    The Exit event (and Enter and Before/AfterUpdate) actually belong to the container Control and not to the textbox, hence they are not available. You also cannot use a WithEvents MSForms.Control variable


    http://www.ozgrid.com/forum/showthread.php?t=154659

  • Re: Add same change event to multiple ComboBoxes


    Quote from pike;769747


    Thanks Pike. This would a nice resource for me. I found mine here in Ozgrid too. It was originally for TextBoxes and I just modified it a bit and change the controls to ComboBoxes.


    But I can't seem to make the Function FillList to run.


    Here's the link . http://www.ozgrid.com/forum/showthread.php?t=80631

  • Re: Add same change event to multiple ComboBoxes


    Quote from pike;769753

    change event is triggered when there is a change in the combobox .. there is no change as its empty .. fill the comboxboxes and change the value .. it will trigger
    but one of the guns will know why


    Actually I tried it too. I added a loop to fill the combobox in UserForm module . But still won't trigger the Function FillList when combobox is changed.

  • Re: Add same change event to multiple ComboBoxes


    Hello
    yes it errors because it is trying to change the combobox that's just changed .. it will trigger but you don't want on change you want on enter which is not in the withevents collection.
    In the userform1 module add the code

    Code
    Option Explicit
    Private CB(1 To 10) As New Class1
     Private Sub UserForm_Initialize()
        Dim i As Long, f As Worksheet
        Set f = Sheets("Sheet1")
        For i = 1 To 10
            CB(i).New_CB Me.Controls("combobox" & i), i
           Me.Controls("ComboBox" & i).List = f.Range("A2", f.[a65000].End(xlUp)).Value
        Next
    End Sub


    in class Class1 module add the code


    it triggers

  • Re: Add same change event to multiple ComboBoxes


    This is the way to go with on enter control class . workbook attached


    classes of this level are very hard to grasp as there are lots of very rare methodologies used

  • Re: Add same change event to multiple ComboBoxes


    Quote from pike;769758

    This is the way to go with on enter control class . workbook attached


    classes of this level are very hard to grasp as there are lots of very rare methodologies used


    Thanks Pike. Yeah, I saw the code earlier and there are lots of methods that I'm not familiar (not surprising though as I'm still new to VBA Excel). Nevertheless, there is never a bad code to learn.


    Thank you for this.

  • Re: Add same change event to multiple ComboBoxes


    Quote from pike;769761

    you picked a doozie to start with


    I've been reading books in between. Just trying to squeeze-in whenever work doesn't ask too much of my time.


    Again, thanks for the help. I'd like to mark this SOLVED but it seems that there's no option to edit the thread title.

  • Re: Add same change event to multiple ComboBoxes


    I'm actually getting a Mismatch type after I implemented it to the original form that I'm working with. Probably because of the other controls like TextBox and ListBox. I tried to removed them and it executed without a problem. I'm looking into putting the other controls inside a Frame as a workaround but it would be nice if I could get it to work without having to resort to Frames.


    EDIT: Putting the other controls inside a Frame does not fix the problem. As soon as the I click inside the Frame the 'Mismatch' will trigger.

  • Re: Add same change event to multiple ComboBoxes


    Hello,
    You can specific control types and add specific routes programmatically by control type or individual control


    http://www.ozgrid.com/VBA/control-loop.htm


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!