ListBox Object -- Inconsistent behavior (sometimes unresponsive?)

[zredbaron]

Hi. I'm attempting to create and hide an ActiveX controls ListBox whenever a user clicks a cell and clicks out of it, respectively. The object has been created manually in the spreadsheet (not via VBA). Attached.

The desired behavior:

• When a user clicks on a cell (E7:E107), to show a ListBox for a user to select an option.
• Clicking another cell (outside of range) should hide the ListBox

The undesired behavior:

• When a user clicks on a cell (E7:E107), the ListBox appears, but it doesn't respond to any clicks
• Clicking another cell (outside of range) hides the ListBox
• Clicking back into (E7:E107), same behavior... no response to clicks.
• HOWEVER... clicking from the range back into the same range (from E10 to E15, for example) DOES WORK and enables the ListBox for mouse listening

I've tried several combinations trying to come up with a magic combination to no avail. What am I missing?

Option Explicit
Dim fillRng As Range
Public RngRoster As Range, RngCategory As Range, RngCategoryList As Range
Public RefRoster As String, RefCategory As String, RefCategoryList As String
Public Sub Initialize_Variables()
    Application.EnableEvents = True
    On Error Resume Next
        Set RngRoster = ThisWorkbook.Names("RangeRoster").RefersToRange
        Set RngCategory = ThisWorkbook.Names("RangeCategory").RefersToRange
        Set RngCategoryList = ThisWorkbook.Names("RangeCategoryList").RefersToRange
        
        RefRoster = ActiveWorkbook.Names("RangeRoster")
        RefCategory = ActiveWorkbook.Names("RangeCategory")
        RefCategoryList = ActiveWorkbook.Names("RangeCategoryList")
    On Error GoTo 0
End Sub
Private Sub ListBoxDemo_Click()
    'MsgBox "list box click"
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
 
    Call Initialize_Variables
    
    'Tests for click in Range E7:E107
    If Not Intersect(Target, RngCategory) Is Nothing Then
        MsgBox "A category assignment was added or changed!" & " (" & Target.Address & ")"
    End If
    
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Call Initialize_Variables
    Dim LBColors As MSForms.ListBox
    Dim LBobj As OLEObject
    Dim i As Long
    
    Set LBobj = Me.OLEObjects("ListBoxDemo")
    Set LBColors = LBobj.Object
    If Not Intersect(Target, RngCategory) Is Nothing Then
        Set fillRng = Target
        With LBobj
            .Left = fillRng.Left
            .Top = fillRng.Top
            .Width = fillRng.Width
            .Locked = False
            .Visible = True
            .Enabled = True
            .Select
        End With
        
    Else
        LBobj.Visible = False
    End If
    ActiveSheet.Select
    Application.EnableEvents = True
    
    
End Sub

Thanks so much in advance!

ListBox Demo.xlsm

[zredbaron]

As a clue for the experts or for anyone with the same issue, I found a (sloppy?) workaround. I simply call a section of code twice in a row. I experimented quite a bit trying to determine what line of code was the magic ingredient, but it appears the whole block is relevant.

This is one of the more bizarre coding behaviors I've seen! Not that bizarre behavior is uncommon.

EDIT-- I thought I had a workaround, but I don't.

I'm still stuck.

[zredbaron]

I have two more clues.
- Programmatically running the Worksheet_SelectionChange Sub does NOT work.
- Clicking and holding the ListBox, and dragging the object a few pixels, seems to reset the object. After dragging it, you can click on it.

Definitely feels like a selection / object focus issue...

[zredbaron]

Found the solution by way of blind luck. Literally scrolled through LBobj.XYZ for applicable methods.... The solution? To Activate the object, as opposed to selecting it.

     If Not Intersect(Target, RngCategory) Is Nothing Then
         Set fillRng = Target
         
         With LBobj
             .Left = fillRng.Left
             .Top = fillRng.Top
             .Width = fillRng.Width
             .Locked = False
             .Enabled = True
             .Activate        '  <---- Magic ingredient
             .Visible = True
         End With
    End If

Note that activating the object after restoring visibility results in the spreadsheet scrolling off screen and confusing the user. Activate before making it visible again!

[p45cal]

Here, .Activate makes the Listbox go all over the place (vertically) and caused the sheet to scroll s othat sometimes the listbox was mostly off screen and nearly never near the cell being filled - ActiveX controls have always had weird and unpredictable behaviour on sheets.
Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call Initialize_Variables
'    Dim LBColors As MSForms.ListBox
'    Dim LBobj As OLEObject
'    Dim i As Long
'    Set LBobj = Me.OLEObjects("ListBoxDemo")
'    Set LBColors = LBobj.Object
With Me.ListBoxDemo
  If Not Intersect(Target, RngCategory) Is Nothing Then
    Set fillRng = Target
    .Top = fillRng.Top
    .Left = fillRng.Left
    .Width = fillRng.Width
    .Verb xlVerbPrimary
    ' .Locked = False
    .Visible = True
    '.Enabled = True
    '.Select
  Else
    .Visible = False
  End If
End With
'ActiveSheet.Select
Application.EnableEvents = True
End Sub

[zredbaron]

Thank you! Unfortunately I'm receiving runtime error 1004: Verb method failed?

.Verb xlVerbPrimary

[p45cal]

Thank you! Unfortunately I'm receiving runtime error 1004: Verb method failed?

.Verb xlVerbPrimary

Mmm.. what version of Excel are you using?

[zredbaron]

MS Office Professional Plus 2013 on Windows 7.