Add event listeners to procedurally generated controls without using a user form

Question

I have a spreadsheet and create ListBox controls in every cell of a column. I'm trying to capture their selected contents but the examples of capturing events on runtime generated controls all involve using a user form and I'm not using one. I'm new to VBA so how can I reproduce the code below from How to add events to Controls created at runtime in Excel with VBA

Option Explicit


Dim ButArray() As New Class2

Private Sub UserForm_Initialize()
    Dim ctlbut As MSForms.CommandButton

    Dim butTop As Long, i As Long

    '~~> Decide on the .Top for the 1st TextBox
    butTop = 30

    For i = 1 To 10
        Set ctlbut = Me.Controls.Add("Forms.CommandButton.1", "butTest" & i)

        '~~> Define the TextBox .Top and the .Left property here
        ctlbut.Top = butTop: ctlbut.Left = 50
        ctlbut.Caption = Cells(i, 7).Value
        '~~> Increment the .Top for the next TextBox
        butTop = butTop + 20

        ReDim Preserve ButArray(1 To i)
        Set ButArray(i).butEvents = ctlbut
    Next
End Sub

My code for generating my controls is

Public Sub CreateListbox()
 Dim rCell As Range
    Dim rRng As Range
    Set rRng = ActiveSheet.Range("AA3:AA45")
    For Each rCell In rRng.Cells
        Set oLISTBOX = ActiveSheet.OLEObjects.Add(classtype:="Forms.ListBox.1")
With oLISTBOX
        .Object.IntegralHeight = False
        .Object.Font.Size = 11
        .Top = rCell.Top
        .Left = rCell.Left
        .Width = rCell.Width
        .Height = rCell.Height
        .LinkedCell = rCell.Address
        .ListFillRange = "ValSocDeterm."
        .Object.ColumnCount = 3
        .MultiSelect = 1
    End With

    Next rCell
End Sub

I basically want to take the example code for creating buttons on a form to creating ListBoxes on a Sheet.

You need to trust VBA model in Options to do the add code way. You can use Event Sinking to do this, then add the new control to a collection of similar controls. You would use a class like so --> Option Explicit Private WithEvents listBox_Custom As MSForms.ListBox Private Sub listBox_Custom_Change() End Sub — Nathan_Sav, Nov 25 '15 at 13:02
Thanks but can you provide a bit clearer explanation, like I said I'm just learning VBA — BBDev, Nov 26 '15 at 12:31

score 0 · Accepted Answer · answered Nov 27 '15 at 10:30

Something like a class module, called clsCustomListBox containing the following code

Option Explicit

Private WithEvents custom As MSForms.ListBox

Public Function initialise(cbConvert As MSForms.ListBox) As Boolean
    Set custom = cbConvert
End Function

Private Sub custom_Click()
    MsgBox "Clicked"
End Sub

and then a standard module to go through the sheet and get all the listboxes, or you could just add to the collection, when your code adds them.

Option Explicit

Private cls_CustomListBox As clsCustomListbox
Public colCustomListboxCollection As Collection

Public Sub GetListBoxes()

Dim c As OLEObject

Set colCustomListboxCollection = New Collection

For Each c In Worksheets("Sheet1").OLEObjects

    If TypeOf c.Object Is MSForms.ListBox Then
        Set cls_CustomListBox = New clsCustomListbox
        cls_CustomCombo.initialise c.Object
        colCustomListboxCollection.Add c
    End If

Next c

End Sub

I havent fully tested as at work, but that's where id start.

Hope it helps.

Add event listeners to procedurally generated controls without using a user form

1 Answers1