PDA

View Full Version : [SOLVED] ListBox Object -- Inconsistent behavior (sometimes unresponsive?)



zredbaron
02-14-2017, 12:43 PM
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!

18372

zredbaron
02-14-2017, 04:23 PM
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
02-14-2017, 05:05 PM
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
02-14-2017, 05:33 PM
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
02-14-2017, 06:19 PM
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
02-15-2017, 02:11 PM
Thank you! Unfortunately I'm receiving runtime error 1004: Verb method failed?


.Verb xlVerbPrimary

p45cal
02-15-2017, 03:02 PM
Thank you! Unfortunately I'm receiving runtime error 1004: Verb method failed?


.Verb xlVerbPrimaryMmm.. what version of Excel are you using?

zredbaron
02-16-2017, 08:40 AM
MS Office Professional Plus 2013 on Windows 7.