ListBox embedded in Frame of OLEobject on worksheet - where is the ListBox Click_Event?

[wjmatthews]

I have a worksheet to which I add an OLEobject as an MSForms Frame. I then add an MSForms listbox inside the frame contained in the OLEObject.Object property.

I want to be able to trigger a click event when the user selects an item from the listbox inside the frame. I can trigger the OLEObject_event by clicking on the frame, but when I click on the list box in the frame, I get nothing. I tried to write a class to add the event to the listBox but no luck.

On another note, when I first create the OLEObjects, I cannot interact with them (fire their events or anything). I need to use the insert>ActiveX Control function from the ribbon in order activate the events of the VBA created ActiveX controls. I'm wondering if the two issues are linked and I'm missing something fundamental here.

Thank you in advance!

Will

Option Explicit

'Declare command array
Dim cmdArr() As New Class1

Sub CreateObjects()


Dim OLEobj As OLEObject
Dim frame As MSForms.frame
Dim lBox As MSForms.ListBox


'Create OLEobj and edit it's properties
Set OLEobj = Sheet1.OLEObjects.Add(ClassType:="Forms.Frame.1", Link:=False, DisplayAsIcon:=False)
With OLEobj
    .Name = "Frame1"
    .Left = 10
    .Top = 10
    .Height = 75
    Set frame = .Object
    'Add lBox to the frame in the OLEobj and populate it
    Set lBox = frame.Controls.Add("Forms.ListBox.1", "ListBox2", True)
    With lBox
        .Left = 5
        .Top = 5
        .Width = 75
        .Height = 50
        .AddItem "Item 1"
        .AddItem "Item 2"
        .AddItem "Item 3"
    End With
    'Add lBox to the commandArr
    ReDim cmdArr(0)
    Set cmdArr(0).CmdEvents = lBox
End With


'Create OLEobj as direct lBox on sheet as comparison
Set OLEobj = Sheet1.OLEObjects.Add(ClassType:="Forms.ListBox.1", Link:=False, DisplayAsIcon:=False)
With OLEobj
    .Name = "ListBox1"
    .Left = 10
    .Top = 100
    Set lBox = OLEobj.Object
    With lBox
        .AddItem "Item 1"
        .AddItem "Item 2"
        .AddItem "Item 3"
    End With
End With
End Sub

'Functions contained within the worksheet
Private Sub Frame1_Click()
MsgBox "Frame Click Event Fired"
End Sub

Private Sub ListBox1_Click()
MsgBox "ListBox1 Click Event Fired"
End Sub

Private Sub ListBox2_Click()
MsgBox "ListBox2 Event Fired"
End Sub



'Code at class module

Option Explicit
Public WithEvents CmdEvents As MSForms.ListBox

Private Sub CmdEvents_Click()
MsgBox "lBox in Frame Click Event Fired"
End Sub

 

[ZVI]

Hi and Welcome to MrExcel board!

Theoretically it is impossible what you are asking for.
But in practice ...
Ok, there is a trick with breaking of execution time by the aid of OnTime with almost zero delay

' Code of Module1
Option Explicit
Dim objLBox(1 To 10) As New Class1
 
Sub CreateObjects()
' ZVI:2014-03-04 http://www.mrexcel.com/forum/excel-questions/768881-listbox-embedded-frame-oleobject-worksheet-where-listbox-click_event.html
  With Sheet1.OLEObjects
    .Delete
    With .Add(ClassType:="Forms.Frame.1", Link:=False, DisplayAsIcon:=False)
      .Name = "Frame1"
      .Left = 10
      .Top = 10
      .Height = 75
      With .Object.Controls.Add("Forms.ListBox.1", "ListBox2", True)
        .Left = 5
        .Top = 5
        .Width = 75
        .Height = 50
        .AddItem "Item 1"
        .AddItem "Item 2"
        .AddItem "Item 3"
      End With
      With .Object.Controls.Add("Forms.ListBox.1", "ListBox2", True)
        .Left = 100
        .Top = 5
        .Width = 75
        .Height = 50
        .AddItem "Item 1"
        .AddItem "Item 2"
        .AddItem "Item 3"
      End With
    End With
  End With
  ' We need to break the execution time here!
  Application.OnTime Now, "LinkIt"
End Sub
 
Private Sub LinkIt()
  ' Link the frame's listboxes to the Class1.CmdEvents
  Dim i As Long
  Dim x As Control
  For Each x In Sheet1.OLEObjects(1).Object.Controls
    If TypeOf x Is MSForms.ListBox Then
      i = i + 1
      Set objLBox(i).CmdEvents = x
    End If
  Next
  ' Refresh Excel to allow selecting the items in listboxes
  On Error Resume Next
  With ActiveSheet
    .Next.Activate
    .Activate
  End With
End Sub

 

[wjmatthews]

Vladimir:

Thank you for the welcome and thank you even more for the solution, I never would have come close to it! You have been a huge help already and I don't want to take up more of your time, but if you feel like it, could you explain (or post a link to) the reason what I was originally trying to do is theoretically impossible? I'm always eager to understand!

Thank you again,

Will