ListBox on Worksheet AddItem

[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

 

[Dave Peterson]

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.

 

[OssieMac]

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]

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

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]

Answered in my other post.

 

[Dave Peterson]

OssieMac...

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

Very nice!

 

[CellShocked]

Answered in my other post.

Cute. Kind of hard to follow the progress when half the dialog is in
another thread.

Got a pointer to that thread?

 

[OssieMac]

http://www.microsoft.com/communitie...&p=1&tid=01e39cb2-4f03-41a2-9950-132f84416c1c