Adding ActiveX Listbox, set its properties, add items, at Runtime

nepai

New member
Joined
Feb 19, 2013
Messages
13
Reaction score
0
Points
0
Hi,

I have this code that when I click a commandbutton, a listbox is created in the sheet.
But I have difficulty adding items on it the moment I click that same button. What I want to do is when that button is click, a list box is created, set the ListStyle = 1, MultiSelect = 1 so that a checkbox will appear, and add items on it. I can do this when I add the listbox in the sheet, but during design time only. I can then add items on it and set the properties in the code. Here's what I got so far. But gives me an error "Runtime error 438".
Code:
Private Sub CommandButton1_Click()
Dim t As Range
Application.ScreenUpdating = False
'ActiveSheet.OLEObjects.Delete
   Set t = ActiveSheet.Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row, 2))
   Set lst = ActiveSheet.OLEObjects.Add("Forms.ListBox.1", Left:=t.Left + 1 _
   , Top:=t.Top, Width:=t.Width - 2, Height:=t.Height * 12)
With lst
    .ListStyle = 1
    .MultiSelect = 1
For i = 1 To 10
    .AddItem ("sample" & i)
Next i
end with
Application.ScreenUpdating = True
End Sub
 
try including the object reference

Code:
Private Sub CommandButton1_Click()
Dim t As Range
Dim lst
Dim i
Application.ScreenUpdating = False
'ActiveSheet.OLEObjects.Delete
   Set t = ActiveSheet.Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row, 2))
   Set lst = ActiveSheet.OLEObjects.Add("Forms.ListBox.1", Left:=t.Left + 1 _
   , Top:=t.Top, Width:=t.Width - 2, Height:=t.Height * 12)
With lst.Object
    .ListStyle = 1
    .MultiSelect = 1
For i = 1 To 10
    .AddItem ("sample" & i)
Next i
End With
Application.ScreenUpdating = True
End Sub
 
That solve it! but there's a problem. When the list is created I cannot select unless I toggle Design Mode button off and then on again. Why is it?
 
Try adding:
Code:
lst.Activate
to the end of the code.
 
Back
Top