none
I am working on a project where in i need to add Combobox and bind withevent at RUNTIME... RRS feed

  • Question

  • there will be at least 50 combobox in each row. i need to bind change event with each combobox runtime....

    Class1 (code)

    Public WithEvents inputObj As MSForms.ComboBox
    
    Private Sub inputObj_Change()
        MsgBox "Change!"
    End Sub
    code to CREATE combobox
    Sub Add_comboBox_first(rw As Integer, col As Integer)
    Dim rng As Excel.Range, cmb As OLEObject
    Dim obj As Class1
    For Each rng In Sheet1.Cells(rw, col)
        With rng
            Set cmb = .Parent.OLEObjects.Add("Forms.ComboBox.1")
            cmb.Top = .Top
            cmb.Left = .Left
            cmb.Width = .Width
            cmb.Height = 20
            cmb.Name = "ComboBox" & rw & col
            cmb.LinkedCell = "$rw$col"
            cmb.Visible = True
         End With
    Next rng
    End Sub

    code to ADD EVENT to combobox

    Dim tbCollection as Collection

    Sub AddEventToCombobox(combo As String) Dim comboject As Object Dim obj As Class1

    Set tbCollection = New Collection Set wbBook = ThisWorkbook Set wsSheet = wbBook.Worksheets("Sheet1") Set comboject = wsSheet.OLEObjects(combo).Object Debug.Print comboject.List(0) Set obj = New Class1 Set obj.inputObj = comboject tbCollection.Add obj End Sub

    the code is working but, change event is not firing...


    • Edited by raavishh Wednesday, August 5, 2015 12:06 PM
    • Moved by KareninstructorMVP Wednesday, August 5, 2015 12:39 PM Moved from VB.NET
    Wednesday, August 5, 2015 12:04 PM

Answers

  • For some reason the code André posted is not visible for me, did you try the code I posted?

    You say you want both functions to run simultaneously, which if I follow you mean you want to create combos individually and add to a withevents class, then maybe later add another combo. As I tried to explain, each time you add a new oleObject all global variables will lose scope as behind the scenes the projects gets recompiled. That includes variables that maintain a reference to the Class objects, in this case a Collection. It's not a problem, later when you new combos you will need to recreate all the withevents classes, and any other variables.

    • Marked as answer by L.HlModerator Thursday, August 13, 2015 9:43 AM
    Thursday, August 6, 2015 9:00 AM
    Moderator

All replies

  • Couple of things, you are creating a new collection each time so previously added withevents calses will lose scope. But also note each time you add a new oleObject in the workbook that's running the code all global variables are destroyed. That means you will need to recreate the classes each time you add a new combo, also any other variables. Maybe you can adapt the  following

    Private tbCollection As Collection
    
    Sub a2()
    Dim rw As Long, cl As Long
    Dim cmb As OLEObject
        For rw = 3 To 15 Step 2
            With Worksheets(1).Cells(rw, 3)
                Set cmb = .Parent.OLEObjects.Add("Forms.ComboBox.1")
                cmb.Top = .Top
                cmb.Left = .Left
                cmb.Width = .Width
                cmb.Height = 20
                cmb.Name = "ComboBox" & rw & col
                cmb.LinkedCell = .Address
                cmb.Visible = True
                cmb.Object.List = Array(1, 2, 3)
            End With
        Next
    
    End Sub
    
    Sub b2()
    Dim wsSheet As Worksheet
    Dim wbBook As Workbook
    Dim ole As OLEObject
    Dim c As Class1
        Set tbCollection = New Collection
        Set wbBook = ThisWorkbook
        Set wsSheet = wbBook.Worksheets("Sheet1")
        For Each ole In ActiveSheet.OLEObjects
            If TypeName(ole.Object) = "ComboBox" Then
                Set c = New Class1
                Set c.inputObj = ole.Object
                tbCollection.Add c
            End If
        Next
    End Sub
    
    Run a2 then b2 


    • Proposed as answer by André Santo Wednesday, August 5, 2015 1:57 PM
    Wednesday, August 5, 2015 1:39 PM
    Moderator
  • Hi André Santo,

    the code you provided worked like charm.

    but, i need to run both the function simultaneously. because i am creating Combobox runtime on selection of a cell and i can't loop through all the combobox each time i create new one. please correct me if i am wrong. i am new to vba excel.

    thanks,

    ravi

    Thursday, August 6, 2015 4:11 AM
  • For some reason the code André posted is not visible for me, did you try the code I posted?

    You say you want both functions to run simultaneously, which if I follow you mean you want to create combos individually and add to a withevents class, then maybe later add another combo. As I tried to explain, each time you add a new oleObject all global variables will lose scope as behind the scenes the projects gets recompiled. That includes variables that maintain a reference to the Class objects, in this case a Collection. It's not a problem, later when you new combos you will need to recreate all the withevents classes, and any other variables.

    • Marked as answer by L.HlModerator Thursday, August 13, 2015 9:43 AM
    Thursday, August 6, 2015 9:00 AM
    Moderator
  • Hi Peter,

    I understood your point.

    I need to create multiple Combobox in row 4 and populate with different item values which is coming from database. initially the comboboxs would be in invisible. but on workbook change event i will check if combobox exists. if yes, will change the property to visible. other wise, will create one in the cell and on the change event of the combobox will enter the selected value in the linked cell and again set property to visible false.

    can you please guide me on how to achieve this.

    thanks in advance.

    Ravi,

    Thursday, August 6, 2015 10:42 AM
  • As already explained, after adding new combos say later in the session, or any time the Collection of Class object references might have lost scope, recreate all the WithEvents classes again with something like the example Sub b2() as posted. You will probably need to adapt this to your own needs, and no doubt the Class will have more Properties which you will need to repopulate individually.
    Thursday, August 6, 2015 1:10 PM
    Moderator