Unable to select any item from activex ListBox Excel 2010

Question

I have some activex ListBox controls on my worksheet. I have applied a macro that opens the litsbox on double_click on any cell.

The issue is that in excel versions 2010 or less, I am unable to select any item from he listbox as the mouse cursor gets stuck in plus sign.

If I zoom in/out the sheet, the issue works for sometime then again gets back to same plus icon state.

This is issue does not occur in excel version above 2010.

Edit: Code snippet:

`Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As 
Boolean)
Application.ScreenUpdating = False
Application.EnableEvents = False

Dim colName, strRange As String
strRange = Target.Address

colName = Replace(Target(1).Address(0, 0), Target(1).row, "")
strRange = colName & CStr(1) & ":" & colName & CStr(6)

If ctrl Is Nothing Then
GoTo ExecuteSub
End If
On Error GoTo ExitSub

listBoxName = "ListBox" + CStr(Target.Column)

On Error GoTo ExitSub
Set ctrl = ActiveSheet.OLEObjects(listBoxName)

If ctrl Is Nothing Then
GoTo ExitSub
Else
Cancel = True

ctrl.Enabled = True
ctrl.Top = Target.Top + Target.Height
ctrl.Left = Target.Left
ctrl.Visible =True`

Longshot... But why don't you try Application.EnableEvents = False right after your Double Click event and see what happens. — K.Dᴀᴠɪs
I have already applied that at the start of double_click and set it to true at the end. — Varsha Khanna
Did you set Cancel = True in the double-click event procedure? — Variatus
Please add a copy of your code that opens the ListBox to your question. — Variatus

Varsha Khanna Varsha Khanna · Accepted Answer · 2018-02-05T16:23:41

After some looking around into forums and support questions, I came up with a probable reason why this is happening. It seems like excel and activex objects don't go well together. Whenever an excel file is zoomed in/out or opened on a screen resolution that was different from its original resolution, excel errors out in its size calculations for various activex objects. Thus, there is temporary distortion with these objects that renders the mouse icon as a plus icon.(unable to select any item). So, the solution will be to resize or reset any size parameter of the activex object(width,height) or by scrolling the active window like a refresh action.

ActiveWindow.SmallScroll ToLeft:=1
ActiveWindow.SmallScroll ToRight:=1

Unable to select any item from activex ListBox Excel 2010

1 Answers