2

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?

Community
  • 1
  • 1
mchac
  • 347
  • 6
  • 21

4 Answers4

2

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

barryleajo
  • 1,956
  • 2
  • 12
  • 13
2

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
snb
  • 333
  • 1
  • 4
  • Thanks snb, I will experiment with it. Is .Parent.Select equivalent to Activesheet.Select? – mchac Jan 31 '15 at 20:41
  • Yes, the activesheet is the 'container'/ 'parent' of the ActiveX-control. – snb Jan 31 '15 at 20:42
  • Thumbs up for that. Clearly more compact than my rookie forms code. I changed ActiveSheet to specify the sheet that's the source (not the activesheet) and went back to "Top:=35, Width:=500, Left:=650, Height:=600". One of the commas was off so the box was in the wrong place plus I can never remember which field is which. – mchac Jan 31 '15 at 20:53
2

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.

Anoorag Saxena
  • 57
  • 1
  • 8
  • It took me a moment to appreciate this solution but then it's the only one on this page that worked for me. I have `Set ListBox = ActiveSheet.OLEObjects.Add` and `ListBox.Activate` does the job. *ActiveSheet.OLEObjects().Activate* gives an error but `Activesheet.OLEObjects("ListBox1").Activate` works as promised. I want to add that the new ListBox can be made to work by manually turning Design Mode off and on again. Doing the same using VBA didn't work because Design Mode turns off VBA. – Variatus Nov 28 '20 at 09:38
0

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.