Google Answers Logo
View Question
 
Q: Excel VBA question: How can I use vba to access a listbox? ( Answered 5 out of 5 stars,   0 Comments )
Question  
Subject: Excel VBA question: How can I use vba to access a listbox?
Category: Computers > Software
Asked by: davidparks21-ga
List Price: $2.00
Posted: 17 Mar 2003 14:30 PST
Expires: 16 Apr 2003 15:30 PDT
Question ID: 177500
I can't find the answer to this anywhere.

I have a listbox displayed in my workbook (note: in my workbook, not
in a form). This listbox has 10 values in it, and I have given the
option of selecting more than one value.

I am now writing a macro that accesses the data chosen by the user. I
cannot find a way to access the list box.  If I set the list box to
only allow one selection then there is a Cell Link option that will
output the results to another cell, however when I set the list box to
multi select it will only output a 0 to this cell, thus I can't access
the information this way.

I need a way to get at this information via VBA. I'm good at VBA and
excel, so a quick and simple example that works is all I need.

Note: the name of my list box is 'MetricLB'

Thanks!
David

Request for Question Clarification by hammer-ga on 17 Mar 2003 16:22 PST
What version of Excel?

- Hammer

Clarification of Question by davidparks21-ga on 17 Mar 2003 18:10 PST
Excel 2000
Answer  
Subject: Re: Excel VBA question: How can I use vba to access a listbox?
Answered By: hammer-ga on 18 Mar 2003 04:47 PST
Rated:5 out of 5 stars
 
The Cell Link is ignored for a MultiSelect List Box. You have to
access the selected items in code. Test the Selected property of each
item in the List to determine which items are selected.

Private Sub ListChoices()
Dim i As Integer
Dim cnt As Integer

    ' Get the number of items in the list
    cnt = ListBox1.ListCount

    ' Loop through the items, checking each one
    ' to see if it is selected
    For i = 0 To (cnt - 1)
        If ListBox1.Selected(i) = True Then
            Debug.Print ListBox1.List(i)
        End If
    Next i

End Sub


Additional Resources:
Look at Excel VBA Help for the ActiveX ListBox Object

- Hammer

Request for Answer Clarification by davidparks21-ga on 18 Mar 2003 06:45 PST
I've tried this but only get an error "Object required"

All I need is a way to refference the list box object from VBA. Where
are you comming up with the object "ListBox1"?

I've used this code:
Sub test1()
x = MetricLB.ListCount
MsgBox x
End Sub

MitricLB is the name of the list box in the excel sheet.  All I need
to know is how to refference this list box from VBA. The rest of the
methods I can figure out on my own.

Thanks!
David

Request for Answer Clarification by davidparks21-ga on 18 Mar 2003 06:54 PST
One other note. I think your example is assuming the listbox is in a
form (I notice a similar example in the documentation which is
refferencing a listbox in a form).

It is not. The list box I'm working with is in the workbook, there is
no form. This is where I can't refference the listbox from.

Thanks,
David

Clarification of Answer by hammer-ga on 18 Mar 2003 07:03 PST
You are referencing the list box correctly, assuming that MetricLB is
actually the name of your list box. Check the properties of the list
box and confirm that you are spelling the name correctly.

I got ListBox1 because I dropped a list box on a spreadsheet to test
this for you and mine was named ListBox1.

In the VBA Editor for your worksheet, there are two dropdowns at the
top of the code window. The one on the left lists the objects on your
worksheet. The first choice is (General). Drop down this list and look
for the name of your list box.

If, after confirming the object name, you are still unable to
reference the list box, I will need to see your Excel spreadsheet to
help you troubleshoot. If necessary, can you post it online where I
can download it?

- Hammer

Clarification of Answer by hammer-ga on 18 Mar 2003 07:06 PST
How did you create your ListBox? I got mine by using the Control Box Toolbar.

- Hammer

Request for Answer Clarification by davidparks21-ga on 18 Mar 2003 07:22 PST
The naming of my list box must be what's wrong.

I created the listbox with the Forms toolbar (view->toolbars->forms).
When I right click on the listbox there is a name in the upper left of
excel (this normally shows the cell refference you currently have
selected).  That is what I changed to MetricLB.

When I created a new list box, it was called "List Box 9" I not the
spaces. This makes me think that this is the wrong refference to the
object.

How can I find what the correct refference to the object is.

In the VBA editor, I know of the two pulldown lists you reffer to, but
these only list "(General)" and "Worksheet" under any of the
worksheets, and just "(General)" under any module, for the left
pulldown list. If I select worksheet it only shows the events for that
worksheet.

Thanks,
David

p.s. I will increase the value of this question to $4 assuming we
figure out what I'm doing wrong here as soon as I figure out how, I
think I have to finalize it to do that.

Clarification of Answer by hammer-ga on 18 Mar 2003 07:37 PST
Typing in the dropdown you mention will have no effect. Excel will try
to select an existing object called MetricLB, and, of course, there
isn't one.

To create a ListBox:

1. From the View Menu, select Toolbars/Control Toolbox. The Control
Toolbox should appear.
2. In the Control Toolbox, click on the ListBox button.
3. Left-click and drag on your worksheet to draw the ListBox. When you
release the mouse button, you should have a ListBox.
4. Right-click on the ListBox and choose Properties.
5. The first Property should be (Name). This tells you the name of
your ListBox. You can change the name by typing over it and saving
your worksheet. You can also change the MultiSelect setting from here.
6. Make your changes, close the Property Editor and save your
worksheet.
7. From the Tools Menu, select Macro/Visual Basic Editor.
8. The tree in the left panel of the VBA Editor should be open on the
last branch. The choices should show something like Sheet1(Sheet1) and
This Workbook. If you have named your worksheets, they will appear by
name. Double-click on your worksheet. NOT "This Workbook". Your
listbox should appear in the dropdown object list along with (General)
and Worksheet.

- Hammer
davidparks21-ga rated this answer:5 out of 5 stars and gave an additional tip of: $2.00
Hot damn that's it. I found it just as you were writing this too.  I
was using the forms toolbar rather than the control toolbar. Evil is
microsoft documentation for not making any mention of the difference
between these two toolbars. I keept reading documentation for the
control toolbar not knowing I was using the wrong one.

Thanks for the help!

David

Comments  
There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy