PDA

View Full Version : ListBox Objects?



jms08h
07-09-2009, 09:02 AM
Hi all,

I have a sub that I want to modify the contents of a listbox. I want to be able to choose the listbox based on certain conditions. Is there any way to capture the listbox in an object or pass a reference?

I've tried,

Dim LBoxA as ListBox
Set LBoxA = ListBox1

But I get a type mismatch error.

If there any way to do this? It would make my code much cleaner to put the ListBox in an object rather than use a bunch of If... Then statements for each Listbox in my sub.

Thanks!

P.S. Excel 2003

JimmyTheHand
07-09-2009, 10:09 AM
Welcome to VBAX :hi:

Try this:
Dim LBoxA As ListBox
Set LBoxA = Sheets(1).Shapes("ListBox1").OLEFormat.Object
I learned this trick just today from XLD :thumb

Jimmy

jms08h
07-09-2009, 10:24 AM
Thanks Jimmy, but this still isn't working.

It sais the specified object isn't found. I think the issue is that ListBox1 is tied to the UserForm and not any specific sheet.

Is there any way to reference the ListBox in a UserForm?

Thanks for the warm welcome!

jms08h
07-09-2009, 10:26 AM
I apologize for not specifying the ListBox was in a userform.

JimmyTheHand
07-09-2009, 01:19 PM
Oh.
Okay, then try
Dim LBoxA As ListBox
Set LBoxA = UserForm1.ListBox1
Explanation:
You need to use a qualifier (I think that's the term) before objects, if you are outside the scope of the object's parent. For example, if you have a userform (named UserForm1) with a listbox (named ListBox1), as long as you are on the code module of UserForm1, you can refer to ListBox1 as ListBox1. Like
Private Sub ListBox1_Change()
MsgBox ListBox1.Value
End Sub

But if you want to access ListBox1 from outside the code module of UserForm1, you must use the UserForm1. qualifier. Like
Sub ExpandList(NewItem As String)
UserForm1.ListBox1.AddItem NewItem
End Sub
And it's only fair, because you can have several listboxes with the name of "ListBox1" (each of them on different userforms, of course) and the compiler needs a way to distinguish between them. Qualifiers are like a full postal address.

With qualifiers, you can even make interactions between listboxes. E.g.
Sub CopyList()
UserForm1.ListBox1.List = UserForm2.ListBox1.List
End Sub

Hope this helps.

Jimmy

jms08h
07-09-2009, 02:14 PM
Private Sub UserForm_Initialize()
Dim LBoxA As ListBox
Set LBoxA = UserForm1.ListBox1
LBoxA.AddItem "Fred"

End Sub



Thanks again for the quick reply. However, qualifying isn't the issue. I understand qualifiers. I did a test with the above code in an otherwise blank project to isolate the issue. This code is written within the form module. I'm beginning to the that the ListBox class is something other than the userform ListBox - perhaps an Access object or maybe even a List in a spreadsheet?

The code is erring on the Set command, not the .AddItem line, just to be clear.

Run-time error '13':

Type mismatch

Is what displays.

Any thoughts?

Thanks again! :cuckoo:

jms08h
07-09-2009, 02:38 PM
Hey Jimmy,

Thanks for all the help. I actually did some extensive digging on this problem and found the answer to this question.

It turns out I was somewhat right, there is more than one object titled ListBox, and the default is the Excel object.

To use the ListBox in the userform or any button in the userform, a specific reference to the MSForms library must be made.

You were right, it's all about qualifiers lol. :)

MSForms.ListBox1 returns the correct object.

Thanks again for all the help.