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.
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?