ListBox on Worksheet AddItem

  • Thread starter Thread starter OssieMac
  • Start date Start date
O

OssieMac

Will very much appreciate it if someone can tell my what I have got wrong in
the example code. I have searched unsuccessfully for the answer.

See comment in code where following error is returned.
Runtime error 438 - Object doesn't support this property or method.

I want to add an ActiveX ListBox with MultiSelect to a Worksheet and then
populate it with AddItem.

Private Sub CommandButton2_Click()
Dim lListBox As Long
Dim tListBox As Long
Dim hListBox As Long
Dim wListBox As Long
Dim lstBox As OLEObject

With ActiveSheet
lListBox = .Cells(2, 2).Left
tListBox = .Cells(2, 2).Top
wListBox = 100
hListBox = 150

Set lstBox = .OLEObjects.Add _
(ClassType:="Forms.ListBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=lListBox, _
Top:=tListBox, _
Width:=wListBox, _
Height:=hListBox)

'Following 2 line work
lstBox.Name = "MyList"
MsgBox lstBox.Name

'****************************************
'Following lines return the error
lstBox.MultiSelect = fmMultiSelectMulti
lstBox.AddItem "Test"

End With

End Sub
 
Try:

lstBox.Object.MultiSelect = fmMultiSelectMulti
lstBox.Object.AddItem "Test"

ps. Instead of using "with activesheet", I'd use "with me"

Me is the keyword that represents the object that owns the code--in this case
the worksheet with the commandbutton.

pps. I'd use double instead of long.
 
Thanks Dave! You are a Gem; That part now works. Also thks for pointing out
the other areas where I'd had a Seniors Moment.

However, I now have another problem. The ListBox is created and populated
but it is locked and cannot select anything. Following code does not unlock
it. However, if I select Design -> Properties then I can see Locked = True
but without changing anything I can just close the Properties and Design View
and I can select the items.

Anyway I did not think that locked had any effect unless the sheet is
protected and the sheet is definitely not protected.

Any thoughts on this? (Tested in both xl2002 and xl2007.)

With lstBox.Object
.MultiSelect = fmMultiSelectMulti
For i = 1 To 10
.AddItem "Test" & i
Next i

'********************************
'Neither of the following do anything
.Enabled = True
.Locked = False
'********************************
End With
 
Because it is really a separate issue, I re-posted my last question in a
separate thread but if anyone sees this and knows the answer then feel free
to answer it here because I will monitor both threads.

--
Regards,

OssieMac


OssieMac said:
Thanks Dave! You are a Gem; That part now works. Also thks for pointing out
the other areas where I'd had a Seniors Moment.

However, I now have another problem. The ListBox is created and populated
but it is locked and cannot select anything. Following code does not unlock
it. However, if I select Design -> Properties then I can see Locked = True
but without changing anything I can just close the Properties and Design View
and I can select the items.

Anyway I did not think that locked had any effect unless the sheet is
protected and the sheet is definitely not protected.

Any thoughts on this? (Tested in both xl2002 and xl2007.)

With lstBox.Object
.MultiSelect = fmMultiSelectMulti
For i = 1 To 10
.AddItem "Test" & i
Next i

'********************************
'Neither of the following do anything
.Enabled = True
.Locked = False
'********************************
End With
 
OssieMac...

Thanks for posting back with the new thread info -- and that it was answered.

Very nice!
 
Back
Top