Excel VBA ActiveX ListBox not allowing click

2
votes

I've created a bit of code that populates an Excel sheet with Lables, TextBoxes and a ListBox. After populating the ListBox with .List=Sheets().Range().Value I am unable to click to select an item. If I save the sheet, close and reopen it works fine.

I've checked http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2014/12/11/forms-controls-stop-working-after-december-2014-updates-.aspx

But I'm not getting an error so this seems the wrong fix.

When I search KB for the following http://support.microsoft.com/kb/3025036/EN-US

The symptoms are different from what I'm experiencing.

I also tried using Sheets().Activate as posted here: Excel ActiveX Listbox not enabled on file open

But that didn't help or I'm implementing it incorrectly.

This is the code that creates the ListBox

Private Sub Create_ListBox_ActiveXControlProperties()

    Dim oLISTBOX As OLEObject

    Set oLISTBOX = ActiveSheet.OLEObjects.Add(classtype:="Forms.ListBox.1", Top:=35, Width:=500, Left:=650, Height:=600)
    ActiveSheet.OLEObjects("ListBox1").Object.Font.Size = 14
    ActiveSheet.OLEObjects("ListBox1").Object.ListStyle = 0
    ActiveSheet.OLEObjects("ListBox1").Object.List = Sheets("Search Criteria Control").Range("g1:g21").Value

End Sub

Can anyone suggest where I can look for a solution to this?

4
vbaexcellistbox

4 Answers

2
votes

Can't give you an explanation but if you add Activesheet.Select to the end of your Sub then you can select your items.

2
votes

Another way of writing:

Sub M_snb()
  With ActiveSheet.OLEObjects.Add("Forms.ListBox.1", , , , , , , 35, 50, 65, 60)
    .Object.Font.Size = 14
    .Object.List = ActiveSheet.Range("g1:g21").Value
    .Object.ListIndex = 0
    .Parent.select
  End With
End Sub
2
votes

For the purposes of documenting all possible solutions, here is what worked for me. None of the workarounds provided in all forums helped me.

I added a bunch of ListBoxes dynamically including location, height, width, ListRange etc. When my code would create the controls in the Worksheet, I wasn't able to select the items in the ListBox.

This is what solved my problem: .OLEObjects().Activate You need to activate the control soon after creating it.

0
votes

Same issue there. As a workaround(app.activate don't works for me and the bahavior isn't wished), you can just add 1 to the height, it will sligthly redraw each object and réactivate it.

ListBox_Options1.Height = ListBox_Options1.Height + 1

It's a workaround, not the source of the bug.